5.4. Ограничения#
5.4. Ограничения
Типы данных - это способ ограничить тип данных, который может быть сохранен в таблице. Однако, для многих приложений, они предоставляют слишком грубое ограничение. Например, столбец, содержащий цену продукта, вероятно, должен принимать только положительные значения. Но нет стандартного типа данных, который принимает только положительные числа. Еще одна проблема заключается в том, что вы можете хотеть ограничить данные столбца относительно других столбцов или строк. Например, в таблице, содержащей информацию о продукте, должна быть только одна строка для каждого номера продукта.
Для этой цели SQL позволяет вам определять ограничения на столбцы и таблицы. Ограничения дают вам столько контроля над данными в ваших таблицах, сколько вам нужно. Если пользователь пытается сохранить данные в столбце, которые нарушают ограничение, возникает ошибка. Это применяется даже в случае, если значение было получено из определения значения по умолчанию.
5.4.1. Проверка ограничений
Проверочное ограничение (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 не принуждает к этому правилу, но вы должны следовать ему, если хотите, чтобы ваши определения таблиц работали с другими системами баз данных). Приведенный выше пример также может быть записан так:
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, описанное в следующем разделе.
Примечание
Tantor SE не поддерживает ограничения CHECK, которые ссылается на данные таблицы, отличные от новой или обновленной строки, которая проверяется. В то время как ограничение CHECK, нарушающее это правило, может работать в простых тестах, оно не может гарантировать, что база данных не достигнет состояния, в котором условие ограничения будет ложным (из-за последующих изменений в других строках, участвующих в ограничении). Это может привести к сбою при создании резервной копии и восстановлении базы данных. Восстановление может завершиться неудачно, даже если полное состояние базы данных согласуется с ограничением, из-за того, что строки не загружаются в порядке, удовлетворяющем ограничению. Если возможно, используйте ограничения UNIQUE, EXCLUDE или FOREIGN KEY для выражения ограничений между строками и таблицами.
Если то, что вам нужно, это однократная проверка других строк при вставке строки, а не непрерывно поддерживаемая гарантия согласованности, можно использовать пользовательский триггер для реализации этого. (Этот подход избегает проблемы с резервным копированием/восстановлением, потому что pg_dump не устанавливает триггеры до восстановления данных, так что проверка не будет применяться во время резервного копирования/восстановления).
Примечание
Tantor SE предполагает, что условия ограничений CHECK являются неизменными, то есть они всегда будут давать одинаковый результат для одной и той же входной строки. Это предположение обосновывает проверку ограничений CHECK только при вставке или обновлении строк, а не в другие моменты времени. (Предупреждение выше о невозможности ссылаться на данные других таблиц на самом деле является особым случаем этого ограничения).
Пример обычного способа нарушения этого предположения - ссылка на пользовательскую функцию в выражении CHECK и изменение поведения этой функции. Tantor SE не запрещает это, но он не заметит, если в таблице появятся строки, нарушающие ограничение CHECK. Это может привести к сбою последующего резервного копирования и восстановления базы данных.
Рекомендуется обрабатывать такое изменение путем удаления ограничения (с помощью ALTER TABLE), изменения определения функции и повторного добавления ограничения, чтобы проверить его для всех строк таблицы.
5.4.2. Ограничения Not-Null
Ограничение NOT NULL просто указывает, что столбец не может принимать значение NULL. Пример синтаксиса:
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
Ограничение not-null всегда записывается как ограничение столбца. Ограничение not-null функционально эквивалентно созданию ограничения проверки CHECK (, но в Tantor SE создание явного ограничения not-null более эффективно. Недостатком является то, что нельзя явно указывать имена для ограничений not-null, созданных таким образом.
column_name
IS 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 для
совместимости с некоторыми другими системами управления базами данных). Однако некоторым пользователям
оно нравится, потому что оно упрощает переключение ограничения в
файле сценария. Например, вы можете начать с:
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);
а затем вставьте ключевое слово NOT в нужных местах.
Подсказка
В большинстве проектов баз данных большинство столбцов должны быть помечены как not null.
5.4.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-tree на столбце или группе столбцов, указанных в ограничении. Ограничение уникальности, охватывающее только некоторые строки, не может быть записано как уникальное ограничение, но можно обеспечить такое ограничение, создав уникальный частичный индекс.
В общем случае, нарушение уникального ограничения происходит, если в таблице есть более одной строки, в которых значения всех столбцов, включенных в ограничение, равны.
По умолчанию, два значения 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, и другие реализации могут иметь отличное поведение. Поэтому будьте осторожны при разработке приложений, предназначенных для переносимости.
5.4.4. Первичные ключи
Ограничение первичного ключа указывает, что столбец или группа столбцов может быть использована в качестве уникального идентификатора для строк в таблице. Это требует, чтобы значения были как уникальными, так и не нулевыми. Таким образом, следующие два определения таблиц принимают одни и те же данные:
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 на столбце или группе столбцов, указанных в первичном ключе, и принудительно установит столбец(ы) как NOT NULL.
Таблица может иметь не более одного первичного ключа. (Может быть любое количество уникальных и not-null ограничений, которые функционально почти то же самое, но только одно из них может быть идентифицировано как первичный ключ.) Теория реляционных баз данных предписывает, что каждая таблица должна иметь первичный ключ. Это правило не принудительно соблюдается Tantor SE, но обычно лучше его следовать.
Первичные ключи полезны как для целей документирования, так и для клиентских приложений. Например, графическое приложение, которое позволяет изменять значения строк, вероятно, должно знать первичный ключ таблицы, чтобы иметь возможность однозначно идентифицировать строки. Существуют также различные способы, с помощью которых система баз данных использует первичный ключ, если он был объявлен; например, первичный ключ определяет столбец(ы) по умолчанию для внешних ключей, ссылающихся на эту таблицу.
5.4.5. Внешние ключи
Ограничение внешнего ключа указывает, что значения в столбце (или группе столбцов) должны соответствовать значениям, появляющимся в некоторой строке другой таблицы. Мы говорим, что это поддерживает ссылочную целостность между двумя связанными таблицами.
Предположим, у вас есть таблица "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) (таким образом, смесь пустых и непустых значений гарантированно не удовлетворит ограничение MATCH FULL). Если вы не хотите, чтобы ссылочные строки могли избежать удовлетворения ограничения внешнего ключа, объявите ссылочный столбец(ы) как NOT NULL.
Ссылочный ключ должен ссылаться на столбцы, которые являются первичным ключом или образуют уникальное ограничение. Это означает, что ссылочные столбцы всегда имеют индекс (тот, который лежит в основе первичного ключа или уникального ограничения); поэтому проверка на наличие соответствующей строки в ссылочной таблице будет эффективной. Поскольку DELETE строки из ссылочной таблицы или UPDATE ссылочного столбца потребует просмотра ссылочной таблицы для строк, соответствующих старому значению, часто имеет смысл также индексировать ссылочные столбцы. Поскольку это не всегда необходимо, и существует много вариантов индексации, объявление ограничения ссылочного ключа не создает автоматически индекс на ссылочных столбцах.
Более подробную информацию о обновлении и удалении данных можно найти в разделе Глава 6. Также ознакомьтесь с описанием синтаксиса ограничений внешнего ключа в справочной документации по CREATE TABLE.
5.4.6. Исключающие ограничения
Ограничения на исключение гарантируют, что если любые две строки сравниваются по указанным столбцам или выражениям с использованием указанных операторов, хотя бы одно из этих сравнений операторов вернет false или null. Синтаксис следующий:
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
См. также CREATE
TABLE ... CONSTRAINT ... EXCLUDE для получения подробной информации.
Добавление исключающего ограничения автоматически создаст индекс указанного типа в объявлении ограничения.