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

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

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

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

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, описанное в следующем разделе.

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

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

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

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

Добавление ограничения уникальности автоматически создает уникальный индекс 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. Сценарий обработки NULL значений по умолчанию в ограничениях уникальности определяется в соответствии со стандартом SQL, и другие реализации могут вести себя по-другому. Поэтому будьте внимательны при разработке переносимых приложений.

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

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-дерево на столбце или группе столбцов, указанных в первичном ключе, и принудительно установит столбец (-ы) как NOT NULL.

Таблица может иметь не более одного первичного ключа. (Может быть любое количество уникальных ограничений и ограничений not-null , которые функционально равнозначны, но только одно из них может быть идентифицировано как первичный ключ.) Теория реляционных баз данных предписывает, что каждая таблица должна иметь первичный ключ. Это правило не является обязательным в Tantor SE-1С, но лучше его соблюдать.

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

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

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

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.

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

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

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

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

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

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

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

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

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

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