5.3. Ограничения

5.3.1. Ограничения-проверки

Ограничение-проверка — наиболее общий тип ограничений. В его определении вы можете указать, что значение данной колонки должно удовлетворять логическому выражению (проверке истинности). Например, цену товара можно ограничить положительными значениями так:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK, за которым идёт выражение в скобках. Это выражение должно включать колонку, для которой задаётся ограничение, иначе оно не имеет большого смысла.

Вы можете также присвоить ограничению отдельное имя. Это улучшит сообщения об ошибках и позволит вам ссылаться на это ограничение, когда вам понадобится изменить его. Сделать это можно так:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

То есть, чтобы создать именованное ограничение, напишите ключевое слово CONSTRAINT, а за ним идентификатор и собственно определение ограничения. (Если вы не определите имя ограничения таким образом, система выберет для него имя за вас.)

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

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

Первые два ограничения определяются похожим образом, но для третьего используется новый синтаксис. Оно не связано с определённой колонкой, а представлено отдельным элементом в списке. Определения колонок и такие определения ограничений можно переставлять в произвольном порядке.

Про первые два ограничения можно сказать, что это ограничения колонок, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений колонок. Ограничения колонок также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение колонки ссылается только на связанную колонку. (Хотя PostgreSQL этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:

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

Или даже так:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

Это дело вкуса.

Ограничениям таблицы можно присваивать имена так же, как и ограничениям колонок:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

Следует заметить, что ограничение-проверка удовлетворяется, если выражение принимает значение true или NULL. Так как результатом многих выражений с операндами NULL будет значение NULL, такие ограничения не будут препятствовать записи NULL в связанные колонки. Чтобы гарантировать, что колонка не содержит значения NULL, можно использовать ограничение NOT NULL, описанное в следующем разделе.

5.3.2. Ограничения NOT NULL

Ограничение NOT NULL просто указывает, что колонке нельзя присваивать значение NULL. Пример синтаксиса:

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

Ограничение NOT NULL всегда записывается как ограничение колонки и функционально эквивалентно ограничению CHECK (имя_колонки IS NOT NULL), но в PostgreSQL явное ограничение NOT NULL работает более эффективно. Хотя у такой записи есть недостаток — назначить имя таким ограничениям нельзя.

Естественно, для колонки можно определить больше одного ограничения. Для этого их нужно просто указать одно за другим:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

Порядок здесь не имеет значения, он не обязательно соответствует порядку проверки ограничений.

Для ограничения NOT NULL есть и обратное: ограничение NULL. Это не означает, что колонка должна иметь только значение NULL, что конечно было бы бессмысленно. Суть же его в простом указании, что колонка может иметь значение NULL (это поведение по умолчанию). Ограничение NULL отсутствует в стандарте SQL и использовать его в переносимых приложениях не следует. (Оно было добавлено в PostgreSQL только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:

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

и затем вставить ключевое слово NOT, где потребуется.

5.3.3. Ограничения уникальности

Ограничения уникальности гарантируют, что данные в определённой колонке или группе колонок уникальны для всех строк таблицы. Ограничение записывается так:

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

в виде ограничения колонки и так:

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

в виде ограничения таблицы.

Если ограничение уникальности определяется для группы колонок, колонки перечисляются через запятую:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

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

Вы можете назначить уникальному ограничению имя обычным образом:

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

При добавлении ограничения уникальности будет автоматически создан уникальный индекс-B-дерево для колонки или группы колонок, задействованных в ограничении. Ограничение уникальности для только некоторых строк можно ввести, создав частичный индекс.

Вообще говоря, ограничение уникальности нарушается, когда в таблице оказывается несколько строк, у которых совпадают значения всех колонок, включённых в ограничение. Однако два значения NULL при сравнении не считаются равными. Это означает, что даже при наличии ограничения уникальности в таблице можно сохранить строки с дублирующимися значениями, если они содержат NULL в одной или нескольких колонках ограничения. Это поведение соответствует стандарту SQL, но мы слышали о СУБД, которые ведут себя по-другому. Имейте в виду эту особенность, разрабатывая переносимые приложения.

5.3.4. Первичные ключи

Технически ограничение первичного ключа представляет собой просто объединение ограничения уникальности c ограничением NOT NULL. Таким образом, следующие два определения создадут таблицу одинаково:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

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

Первичные ключи, как и ограничения уникальности, могут включать несколько колонок и записывается это так же:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

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

При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для связанной колонки или группы колонок.

Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что в каждой таблице должен быть первичный ключ. В PostgreSQL такого жёсткого требования нет, но обычно лучше ему следовать.

5.3.5. Внешние ключи

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

Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:

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

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

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

С таким ограничением создать заказ со значением product_no, отсутствующим в таблице products (и не равным NULL), будет невозможно.

В такой схеме таблицу orders называют подчинённой таблицей, а products — главной. Соответственно, колонки называют так же подчинённой и главной (или ссылающейся и целевой).

Предыдущую команду можно сократить так:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

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

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

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

Естественно, число и типы колонок в ограничении должны соответствовать числу и типам целевых колонок.

Ограничению внешнего ключа можно назначить имя стандартным способом.

Таблица может содержать несколько ограничений внешнего ключа. Это полезно для связи таблиц в отношении многие-ко-многим. Скажем, у вас есть таблицы продуктов и заказов, но вы хотите, чтобы один заказ мог содержать несколько продуктов (что невозможно в предыдущей схеме). Для этого вы можете использовать такую схему:

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

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Заметьте, что в последней таблице первичный ключ покрывает внешние ключи.

Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к одному продукту. Но что делать, если после создания заказов с определённым продуктом мы захотим удалить его? SQL справится с этой ситуацией. Интуиция подсказывает следующие варианты поведения:

• Запретить удаление продукта

• Удалить также связанные заказы

• Что-то ещё?

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

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

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Ограничивающие и каскадные удаления — два наиболее распространённых варианта. RESTRICT предотвращает удаление связанной строки. NO ACTION означает, что если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка (это поведение по умолчанию). (Главным отличием этих двух вариантов является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT — нет.) CASCADE указывает, что при удалении связанных строк зависимые от них будут так же автоматически удалены. Есть ещё два варианта: SET NULL и SET DEFAULT. При удалении связанных строк они назначают зависимым колонкам в подчинённой таблице значения NULL или значения по умолчанию, соответственно. Заметьте, что это не будет основанием для нарушения ограничений. Например, если в качестве действия задано SET DEFAULT, но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция закончится ошибкой.

Аналогично указанию ON DELETE существует ON UPDATE, которое срабатывает при изменении заданной колонки. При этом возможные действия те же, а CASCADE в данном случае означает, что изменённые значения связанных колонок будут скопированы в зависимые строки.

Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если одна из связанных колонок содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL, строка будет удовлетворять ограничению, только если все связанные колонки равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные колонки как NOT NULL.

Внешний ключ должен ссылаться на колонки, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных колонок всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно. Так как команды DELETE для строк главной таблицы или UPDATE для зависимых колонок потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых колонок. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным колонкам.

Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE.

5.3.6. Ограничения-исключения

Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным колонкам или выражениям с помощью заданных операторов, минимум одно из этих сравнений возвратит false или NULL. Записывается это так:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Подробнее об этом см. CREATE TABLE ... CONSTRAINT ... EXCLUDE.

При добавлении ограничения-исключения будет автоматически создан индекс того типа, который указан в объявлении ограничения.