Ограничение 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)
);

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

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

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 считается выполненным, если проверочное выражение принимает значение true или null. Поскольку большинство выражений возвращают null, если хотя бы один операнд равен null, они не запрещают наличие значений null в столбцах с такими ограничениями. Чтобы гарантировать, что столбец не содержит значений null, можно использовать ограничение NOT NULL, которое описывается в следующем разделе.

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

CREATE TABLE products (
    product_no integer NOT NULL,
    name текст NOT NULL,
    price numeric
);

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

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

а затем вставить NOT ключевое слово там, где это необходимо.

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

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

Особенностью реализации ограничения уникальности в Digital Q.DataBase является возможность определения уникальности не только на основе значения в столбце, но и на основе выражения над значением столбца. Общий синтаксис создания таблицы с таким ограничением:

CREATE TABLE table_name (
    ...
    [ CONSTRAINT constraint_name ] UNIQUE [ NULLS [ NOT ] DISTINCT ] (expression)
    [ INCLUDE (column_name [, ...]) ]
);

Пример создания таблицы с ограничением уникальности для абсолютного значения столбца:

CREATE TABLE reference (
    cost INTEGER,
    CONSTRAINT reference_cost_constraint UNIQUE (abs(cost)) DEFERRABLE INITIALLY DEFERRED
);

В этом примере ограничение уникальности устанавливается для выражения abs(cost), где cost — имя столбца таблицы reference.

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

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

Пример добавления ограничения уникальности после создания таблицы:

CREATE TABLE reference (
    cost INTEGER
);
ALTER TABLE reference ADD CONSTRAINT reference_cost_constraint UNIQUE (abs(cost)) DEFERRABLE INITIALLY DEFERRED;

Пример добавления ограничения уникальности для нескольких столбцов таблицы:

CREATE TABLE reference (
    cost INTEGER,
    add_cost INTEGER
);
ALTER TABLE reference ADD CONSTRAINT reference_cost_constraint UNIQUE (add_cost, abs(cost));

В этом примере ограничение уникальности устанавливается для выражения abs(cost) и столбца add_cost.

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

В общем случае ограничение уникальности нарушается, если в таблице присутствует более одной строки, в которых значения всех столбцов, входящих в ограничение, равны. По умолчанию два значения NULL не считаются равными при таком сравнении. Это означает, что даже при наличии ограничения уникальности можно сохранять дублирующиеся строки, содержащие значение NULL по крайней мере в одном из столбцов, на которые наложено ограничение. Данное поведение можно изменить, добавив выражение NULLS NOT DISTINCT, например

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

или

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

Поведение по умолчанию может быть явно указано с помощью NULLS DISTINCT. Согласно стандарту SQL, обработка значений NULL в ограничениях уникальности по умолчанию определяется конкретной реализацией, и другие СУБД могут иметь иное поведение. Поэтому следует соблюдать осторожность при разработке приложений, которые должны быть переносимыми.

PRIMARY KEY указывает на то, что столбец или группа столбцов могут использоваться в качестве уникального идентификатора для строк в таблице. Это требует, чтобы значения были одновременно уникальными и непустыми (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)
);

Добавление первичного ключа автоматически создает уникальный индекс типа B-tree для столбца или группы столбцов, указанных в ограничении PRIMARY KEY, и принудительно помечает данные столбцы как NOT NULL. NOT NULL.

Таблица может иметь не более одного первичного ключа. (Может существовать любое количество ограничений уникальности и NOT NULL, которые функционально почти идентичны, но только одно из них может быть определено как первичный ключ.) Теория реляционных баз данных предписывает, что каждая таблица должна иметь первичный ключ. Данное правило не обеспечивается принудительно Digital Q.DataBase, но обычно его лучше придерживаться.

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

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

Допустим, имеется Таблица products, которую мы уже использовали несколько раз:

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 со значениями, отличными от NULL, product_no которые отсутствуют в Таблице products.

В данной ситуации принято говорить, что таблица 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 tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

Узел верхнего уровня будет иметь значение NULL parent_id, в то время как значения, отличные от NULL parent_id будут ограничены требованием ссылаться на существующие строки таблицы.

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

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 зависит от того, какие типы объектов представляют связанные таблицы. Когда ссылающаяся таблица представляет собой компонент того, что представлено в таблице, на которую ссылаются, и не может существовать независимо, тогда CASCADE может быть уместным. Если две таблицы представляют независимые объекты, то RESTRICT или NO ACTION будет более подходящим; Приложение, которому требуется удалить оба объекта, должно явно инициировать это действие, выполнив две отдельные команды удаления. В приведенном выше примере позиции заказа являются частью заказа, поэтому удобно, когда они удаляются автоматически при удалении основного заказа. Однако продукты и заказы представляют собой разные сущности, и автоматическое удаление позиций заказа при удалении продукта может быть нежелательным. Действия SET NULL или SET DEFAULT могут быть целесообразны, если связь по внешнему ключу представляет необязательную информацию. Например, если таблица products содержит ссылку на менеджера по продукту, и запись о менеджере удаляется, может быть полезно установить для ссылки на менеджера в таблице продуктов значение NULL или значение по умолчанию.

Для действий SET NULL и SET DEFAULT можно указать список столбцов. Как правило, устанавливаются все столбцы ограничения внешнего ключа; изменение только части столбцов может быть полезно в некоторых специальных случаях. Рассмотрим следующий пример:

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);

Без указания столбца внешний ключ также установил бы в null столбец tenant_id , но данный столбец все еще требуется в качестве части первичного ключа.

Аналогично ON DELETE также существует ON UPDATE , которое вызывается при изменении (обновлении) ссылаемого столбца. Набор возможных действий совпадает, за исключением того, что списки столбцов нельзя указывать для SET NULL и SET DEFAULT. В этом случае CASCADE означает, что обновленные значения ссылаемого столбца (или столбцов) должны быть скопированы в ссылающиеся строки.

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

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

Дополнительная информация об обновлении и удалении данных приведена в Глава 2.3. Также см. описание синтаксиса ограничения внешнего ключа в справочной документации для CREATE TABLE.

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

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

См. также CREATE TABLE ... CONSTRAINT ... EXCLUDE для получения подробной информации.

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