DDL. CONSTRAINT

Дорогу осилит идущий

В сегодняшней статье речь пойдет об инструментах валидации - CONSTRAINT’ах (они же - ограничения, констрэинты), которые предоставляет SQL.

Именно этот механизм позволяет указать правила, которым должно соответствовать значение при вставке в конкретную колонку таблицы. Сюда можно отнести допустимость null-значения, допустимый набор значений в целом, уникальность (в т.ч. по нескольким колонкам) и т.д.

Как можно догадаться, PK и FK тоже являются constraint’ами.

В зависимости от СУБД, различные констрэинты могут иметь различный синтаксический сахар, позволяющий указывать их более-менее лаконично, поскольку базовый синтаксис выглядит относительно громоздко и не всегда удобен для восприятия.

В рамках статьи мы разберем как каноническое описание ограничений при создании (и изменении) таблицы, так и некоторые из примеров упрощенного синтаксиса в pg - тем более, с некоторыми из них мы уже познакомились.

Виды constraint’ов

CHECK. Вид ограничений, который позволяет указать boolean-выражения (как для WHERE, например). Запись может быть вставлена только если она соответствует всем указанным условиям;
NOT NULL. Полагаю, суть очевидна из названия. Указывает, что в колонке, для которой создан этот constraint, нельзя указывать null-значения. По сути, частный случай CHECK-constraint’а (column_name is not null). Но в конкретных СУБД, включая postgres, оптимизирован и может быть выгоднее в использовании, нежели эквивалентный ему CHECK.;
UNIQUE. Указывает, что значение в колонке (совокупность значений, если констрэинте участвует несколько колонок) должно быть уникально в рамках таблицы. Стоит помнить, что NULL != NULL в большинстве популярных реляционных СУБД, что может приводить к неприятным эффектам;
PRIMARY KEY. Суть этого вида ограничения мы разбирали в рамках прошлого урока. С точки зрения вставки он идентичен UNIQUE + NOT NULL. Но накладывает также дополнительные ограничения на удаление записи или изменение колонок, составляющих PK;
FOREIGN KEY. Опять же, разбирали в рамках прошлого урока. Единственное ограничение, накладываемое FK при вставке (и изменении) - в связанной таблице должно существовать соответствующее значение. Остальные ограничения FK накладывает на соответствующий PK, а не на записи таблицы, в которой он задан;
EXCLUDE. Специфический и достаточно редкий вид constraint’а, который специфический вид “уникальности” строки. Регламентирует, что при сравнении указанными операторами (например, “=”) любых двух строк таблицы, хотя бы одно из сравнений вернет NULL или false (иными словами - не true). Мы не будем глубоко разбирать этот вид ограничений, но ниже будет ссылка на пример использования, которая, на мой взгляд, наглядно демонстрирует суть. В текстовом формате не получается описать доходчиво:(

Синтаксис

Как оказалось, русскоязычная документация весьма хорошо описывает constraint’ы в pg, включая примеры использования как в виде базового синтаксиса, так и с доступным синтаксическим сахаром. Поэтому вместо примеров для CREATE-запросов буду ссылаться на нее.

В целом, рекомендую полностью прочесть информацию по ссылке, она подается в достаточно понятном виде, хоть и многословно. Кроме, пожалуй, EXCLUDE, но для него у меня есть другой пример.

Собственно, основная ссылка (дальше буду линковать конкретные подразделы из этой статьи): ссылка

CHECK

Использование при CREATE TABLE: ссылка

Для ALTER TABLE все будет выглядеть достаточно похоже (здесь и далее - буду использовать в примерах таблицы из документации).

Допустим, что таблица была создана products без указания constraint’ов:

CREATE TABLE products (
  product_no         integer,
  name               text,
  price              numeric,
  discounted_price   numeric
);

Тогда добавление check-ограничений может выглядеть так:

alter table products
  add check (price > 0),
  add check (discounted_price > 0),
  add check (price > discounted_price);

Или можем добавлять их именованными (на примере price > 0):

alter table products add constraint positive_price check (price > 0);

Обратите внимание: для всех видов ограничений (кроме NOT NULL), можно задать собственное имя CONSTRAINT’а. Рекомендую пользоваться этой возможностью в тех случаях, когда вы вынуждены описывать constraint за пределами отдельного столбца - по сути, все случаи, когда ограничение затрагивает более одной колонки. Это позволяет проще отслеживать историю конкретного constraint’а (например, если он был добавлен, а после удален). Глобально, для этих же целей есть смысл именовать и другие виды ограничений, но обычно это менее чувствительно.

В чем суть этой рекомендации, полагаю, станет понятно, когда мы познакомимся с инструментами миграций и посмотрим на это с практической точки зрения.

NOT NULL

Вероятно, самый простой в плане синтаксиса constraint: ссылка

Рекомендую обратить внимание, что для одной колонки можно задать сразу несколько ограничений (что логично). Запись этих ограничений можно делать после типа данных колонки (если указывать при создании таблицы), разделяются констрэинты только пробелами.

Если же указывать отдельно от колонок (или при изменении таблицы) - общий синтаксис для отдельных элементов с разделением через запятую.

Для ALTER все тоже просто:

alter table products alter column product_no set not null;

UNIQUE

Ссылка на документацию: ссылка

Тут впервые в тексте встречается термин индекс. Мы будем знакомиться с индексами и их видами в ближайших уроках, пока же можно пропустить эту часть информации.

Варианты для ALTER:

alter table products add unique (product_no);

alter table products 
  add constraint unique_product_no unique (product_no);

PRIMARY и FOREIGN KEY

В целом, мы разбирали эти виды constraint’ов в рамках прошлого урока, но повторение не повредит: PK, FK.

К слову, по какой-то причине в документации опущен способ указания PK/FK через явное указание CONSTRAINT. Поэтому дополню.

PK:

create table example (
  a integer,
  b integer,
  c integer,
  constraint example_primary_key primary key(a, c)
);

Вариант добавления PK при ALTER:

alter table example 
add constraint example_primary_key primary key(a, c);

И, соответственно, FK:

create table t1 (
  a integer primary key,
  b integer,
  c integer,
  constraint example_foreign_key foreign key (b, c) 
    references example (a, c)
);

Для ALTER:

alter table t1 add constraint example_foreign_key foreign key (b, c) references example (a, c);

Как видите, все достаточно однотипно и, полагаю, очевидно. Такой способ создания PK/FK однозначно нельзя назвать популярным, но он, на мой взгляд, важен при изучении, поскольку демонстрирует принадлежность этих механик к констрэинтам.

EXCLUDE

Как и говорил выше, я не вижу особого смысла углубляться в этот вид ограничений в рамках курса. Но предлагаю удачную, на мой взгляд, демонстрацию использования такого вида ограничений: ссылка

Суть в том, чтобы избежать активной брони одной и той же комнаты в отеле на пересекающиеся периоды времени. Кроме синтаксиса самого ограничения, новым может оказаться функция tstzrange и оператор &&. Имхо, это не вредит пониманию общей идеи, но для любопытных:

tstzrange - диапазонный тип для периода от timestamptz_1 до timestamptz_2, и, соответственно, одноименная функция для создания элемента этого типа;

&& - один из операторов для работы с подобными типами. Обозначает пересечение. Семантически, проверка в примере эквивалентна знакомому нам оператору OVERLAPS. Но его не удалось бы указать в описании констрэинта из-за особенностей синтаксиса.

С теорией на сегодня все!

Переходим к практике:

Задача

Добавьте в рамках тестовой БД необходимые UNIQUE и NOT NULL ограничения сущностям в соответствии с их ролью. При необходимости можете использовать и иные виды констрэинтов.

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!