5.3. Ограничения целостности

5.3.1. Ограничения Check

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

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

Как видите, определение ограничения следует после указания типа данных, как и определение значения по умолчанию. Значения по умолчанию и ограничения могут следовать в произвольном порядке. Ограничение check состоит из ключевого слова CHECK, за которым следует выражение в круглых скобках. Выражение в ограничении check должно использовать ту колонку, на которую оно накладывается, в противном случае это ограничение не будет иметь смысла.

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

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

Таким образом, для создания именованого ограничения, используйте ключевое слово CONSTRAINT, за которым следует индентификатор (имя), после которого следует, собственно, определение ограничения. (Если при использовании этого синтаксиса вы не укажете имя ограничения, СУБД выберет имя за вас.)

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

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)
);

Необходимо заметить, что ограничение check удовлетворяется, если выражение, указанное в ограничении возвращает значение истина или null. Поскольку большинство выражений возвращает значение null, если любой из операндов имеет значение null, то использование выражений не предотвратит появление значений null в колонках, на которые наложены ограничения. Чтобы иметь уверенность в том, что колонка не будет содержать значений null, можно использовать ограничение не-null, которое описывается в следующей секции.

5.3.2. Ограничения не-Null

Ограничение не-null просто говорит, что колонка не должна содержать значение null. Пример синтаксиса:

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

Ограничение не-null всегда записывается как ограничение на колонку. Ограничение не-null функционально эквивалентно созданию ограничения CHECK (column_name IS NOT NULL), но в PostgreSQL создание явного ограничения не-null является более эффективным. Недостатком является то, что вы не можете явно задавать имена ограничениям не-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
);

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

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

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

Технически, ограничение первичного ключа просто является комбинацией ограничений уникальности и не-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). Такой идентификатор полезен как для целей документирования, так и для клиентских приложений. Например, приложение с графическим интерфейсом, которое позволяет изменять значения строк наверняка будет нуждаться в первичном ключе таблицы, чтобы идентифицировать нужные строки.

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

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

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

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

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

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

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

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

Теперь в таблице orders невозможно создать строку, в которой значение product_no не соответствует одному из значений в таблице products.

Мы говорим, что в данной ситуации, таблица orders является ссылающейся (referencing) таблицей, а таблица products является ссылочной (referenced) таблицей. Похожим образом, колонки являются ссылающейся и ссылочной.

Вы можете записать данную выше команду в более кратком виде

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)
);

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

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

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

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)
);

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

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

• Запретить удаление ссылочного продукта

• Всё-равно удалить строку

• Что-то другое?

Чтобы проиллюстрировать это, реализуем следующую политику отношения многим-ко-многим в предыдущем примере: когда кто-то хочет удалить товар, на который ссылается строка в таблице orders (через order_items), мы запрещаем это. Если кто-то удаляет строку в orders, элменты в 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, вызов действия по которому происходит, когда ссылочная колонка изменяется (обновляется). Список возможных действий тот же самый.

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

Больше информации об обновлении и удалении данных можно найти в Chapter 6.

В завершении, мы должны отметить, что внешний ключ должен ссылаться на колонки, которые либо являются первичным ключом, либо формой ограничения уникальности. Если внешний ключ ссылается на ограничение уникальности, то необходимо обратить внимание на некоторые дополнительные вещи, такие как совпадение со значениями null. Об этом рассказывается в справочной документации CREATE TABLE.

5.3.6. Исключение ограничений

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

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

Подробности см. также в CREATE TABLE ... CONSTRAINT ... EXCLUDE.

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