Ограничения

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

Исходя из этого, SQL позволяет определять ограничения для столбцов и таблиц. Ограничения дают возможность сколько угодно управлять данными в таблицах. Если пользователь пытается сохранить в столбце данные, нарушающие ограничение, возникает ошибка. Это происходит, даже если значение пришло из определения значения по умолчанию.

---

---

Проверочные ограничения

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

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

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

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

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

Примечание
QHB не поддерживает ограничения CHECK, ссылающиеся на данные таблицы, кроме проверки новой или измененной строки. Хотя проверочное ограничение нарушающее это правило, может показаться работающим в простых тестах, это не гарантирует, что база данных не достигнет состояния, в котором условие ограничения ложно (из-за последующих изменений другой строки (или строк)). Это может привести к сбою выгрузки и восстановления базы данных. Сбой при восстановлении может произойти, даже если полное состояние базы данных согласуется с ограничением, из-за того, что строки загружаются не в том порядке, который будет удовлетворять этому ограничению. Для выражения ограничений между строками и между таблицами, по возможности, используйте ограничения UNIQUE, EXCLUDE или FOREIGN KEY.

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

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

Примером распространенного способа нарушить это предположение является ссылка на пользовательскую функцию в выражении CHECK, а затем изменение поведения этой функции. QHB не запрещает этого, но не заметит, есть ли в таблице строки, которые теперь нарушают ограничение CHECK. Это может привести к сбою последующих выгрузки и восстановления базы данных. Рекомендуемый способ проведения такого изменения состоит в том, чтобы удалить ограничение (командой ALTER TABLE), скорректировать определение функции и повторно добавить ограничение, тем самым перепроверив его для всех строк таблицы.

---

---

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

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

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

Ограничение NOT NULL всегда записывается как ограничение столбца. Ограничение NOT NULL функционально равнозначно созданию проверочного ограничения CHECK (имя_столбца IS NOT NULL), но в QHB создание явного ограничения 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 и не должно использоваться в переносимых приложениях. (Оно было добавлено в QHB только для совместимости с некоторыми другими СУБД.) Однако некоторым пользователям оно нравится, так как позволяет легко переключать ограничение в файле скрипта. Например, можно начать с:

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

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

Совет
В основном в базах данных при проектировании большинство столбцов должно быть помечено как NOT NULL.

---

---

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

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

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

---

---

Ограничения первичного ключа

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

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

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

---

---

Ограничения внешнего ключа

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

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

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

Теперь невозможно создавать заказы с отличными от NULL записями product_no, отсутствующими в таблице товаров.

В этой ситуации мы называем таблицу заказов ссылающейся таблицей, а таблицу товаров — ссылочной. Аналогичным образом именуются ссылающиеся и ссылочные столбцы.

Указанную выше команду можно сократить до:

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

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

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

---

---

Исключающие ограничения

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

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

Подробную информацию см. на справочной странице команды CREATE TABLE ... CONSTRAINT ... EXCLUDE.

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