PostgreSQL уникальные ограничения
В этом учебном пособии вы узнаете, как создавать, добавлять и удалять уникальные ограничения в PostgreSQL с синтаксисом и примерами.
Что такое уникальное ограничение в PostgreSQL?
Уникальное ограничение — это отдельное поле или комбинация полей, которые однозначно определяют запись. Некоторые поля могут содержать нулевые значения, если комбинация значений уникальна.
В чем разница между уникальным ограничением и первичным ключом?
| Первичным ключ | Уникальное ограничение |
|---|---|
| Ни одно из полей, которые являются частью первичного ключа, не может содержать нулевое значение. | Некоторые поля, являющиеся частью ограничения уникальности, могут содержать нулевые значения, если комбинация значений уникальна. |
Создать уникальное ограничение — использование оператора CREATE TABLE
Синтаксис для создания уникального ограничения с помощью оператора CREATE TABLE в PostgreSQL:
PostgreSQL — Ограничения в таблицах
Типы данных — это способ ограничить тип данных, которые можно хранить в таблице. Однако для многих приложений ограничения, которые они обеспечивают, слишком грубы. Например, столбец, содержащий цену продукта, вероятно, должен принимать только положительные значения. Но не существует стандартного типа данных, который принимает только положительные числа. Другая проблема заключается в том, что вы можете захотеть ограничить данные столбца по отношению к другим столбцам или строкам. Например, в таблице, содержащей информацию о продукте, для каждого номера продукта должна быть только одна строка.
С этой целью 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) ); Первые два ограничения должны показаться вам знакомыми. Третий использует новый синтаксис. Он не привязан к конкретному столбцу, а отображается как отдельный элемент в списке столбцов, разделенных запятыми. Определения столбцов и определения этих ограничений могут быть перечислены в смешанном порядке.
Мы говорим, что первые два ограничения являются ограничениями столбца, тогда как третье ограничение является ограничением таблицы, потому что оно записывается отдельно от любого определения столбца. Ограничения столбца также могут быть записаны как ограничения таблицы, хотя обратное не обязательно возможно, поскольку предполагается, что ограничение столбца относится только к столбцу, к которому оно прикреплено. ( 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) ); Следует отметить, что проверочное ограничение удовлетворяется, если проверочное выражение оценивается как истинное или нулевое значение. Поскольку большинство выражений будут оцениваться как нулевое значение, если какой-либо операнд имеет значение null, они не предотвратят нулевые значения в столбцах с ограничениями. Чтобы гарантировать, что столбец не содержит нулевых значений, можно использовать ограничение ненулевого значения, описанное в следующем разделе.
PostgreSQL не поддерживает CHECK ограничения, ссылающиеся на данные таблицы, отличные от проверяемой новой или обновленной строки. Хотя CHECK в простых тестах может показаться, что ограничение, нарушающее это правило, работает, оно не может гарантировать, что база данных не достигнет состояния, в котором условие ограничения ложно (из-за последующих изменений других задействованных строк). Это приведет к сбою дампа базы данных и перезагрузки. Повторная загрузка может завершиться ошибкой, даже если полное состояние базы данных соответствует ограничению, из-за того, что строки загружаются не в том порядке, который удовлетворяет ограничению. Если возможно, используйте ограничения UNIQUE , EXCLUDE или FOREIGN KEY для выражения ограничений между строками и таблицами.
Если вам нужна однократная проверка других строк при вставке строки, а не постоянно поддерживаемая гарантия согласованности, для этого можно использовать настраиваемый триггер . (Этот подход позволяет избежать проблемы дампа/перезагрузки, поскольку pg_dump не переустанавливает триггеры до перезагрузки данных, поэтому проверка не будет выполняться во время дампа/перезагрузки.)
PostgreSQL предполагает, что CHECK условия ограничений неизменны, то есть они всегда будут давать один и тот же результат для одной и той же входной строки. Именно это предположение оправдывает рассмотрение CHECK ограничений только при вставке или обновлении строк, а не в другое время. (Приведенное выше предупреждение о том, что нельзя ссылаться на другие табличные данные, на самом деле является частным случаем этого ограничения.)
Примером распространенного способа нарушить это предположение является ссылка на определяемую пользователем функцию в CHECK выражении, а затем изменение поведения этой функции. PostgreSQL не запрещает это, но не заметит, если в таблице есть строки, нарушающие CHECK ограничение. Это приведет к сбою последующего дампа и перезагрузки базы данных. Рекомендуемый способ обработки такого изменения — удалить ограничение (используя ALTER TABLE ), скорректировать определение функции и повторно добавить ограничение, тем самым перепроверив его по всем строкам таблицы.
Ненулевые ограничения
Ограничение not-null просто указывает, что столбец не должен принимать нулевое значение. Пример синтаксиса:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric ); Непустое ограничение всегда записывается как ограничение столбца. Ненулевое ограничение функционально эквивалентно созданию проверочного ограничения , но в PostgreSQL создание явного ненулевого ограничения более эффективно. Недостатком является то, что вы не можете давать явные имена ненулевым ограничениям, созданным таким образом. CHECK (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 и не должно использоваться в переносимых приложениях. (Оно было добавлено в PostgreSQL только для совместимости с некоторыми другими системами баз данных.) Некоторым пользователям, тем не менее, это нравится, поскольку позволяет легко переключать ограничения в файле скрипта. Например, вы можете начать с:
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 ); Добавление уникального ограничения автоматически создаст уникальный индекс сбалансированного дерева для столбца или группы столбцов, перечисленных в ограничении. Ограничение уникальности, охватывающее только некоторые строки, не может быть записано как ограничение уникальности, но можно применить такое ограничение, создав уникальный частичный индекс .
Как правило, ограничение уникальности нарушается, если в таблице имеется более одной строки, в которой значения всех столбцов, включенных в ограничение, равны. Однако два нулевых значения никогда не считаются равными в этом сравнении. Это означает, что даже при наличии уникального ограничения можно хранить повторяющиеся строки, содержащие нулевое значение по крайней мере в одном из столбцов с ограничениями. Это поведение соответствует стандарту SQL, но мы слышали, что другие базы данных 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) ); Добавление первичного ключа автоматически создаст уникальный индекс сбалансированного дерева для столбца или группы столбцов, перечисленных в первичном ключе, и принудительно пометит столбцы NOT NULL .
Таблица может иметь не более одного первичного ключа. (Может быть любое количество уникальных и ненулевых ограничений, которые функционально почти одно и то же, но только одно из них может быть идентифицировано как первичный ключ.) Теория реляционных баз данных диктует, что каждая таблица должна иметь первичный ключ. Это правило не применяется PostgreSQL , но обычно лучше следовать ему.
Первичные ключи полезны как для документирования, так и для клиентских приложений. Например, приложение с графическим интерфейсом, позволяющее изменять значения строк, возможно, должно знать первичный ключ таблицы, чтобы иметь возможность однозначно идентифицировать строки. Существуют также различные способы использования системой баз данных первичного ключа, если он был объявлен; например, первичный ключ определяет целевые столбцы по умолчанию для внешних ключей, ссылающихся на его таблицу.
Внешние ключи
Ограничение внешнего ключа указывает, что значения в столбце (или группе столбцов) должны совпадать со значениями, отображаемыми в некоторой строке другой таблицы. Мы говорим, что это поддерживает ссылочную целостность между двумя связанными таблицами.
Скажем, у вас есть таблица продуктов, которую мы уже использовали несколько раз:
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 записями, которых нет в таблице товаров.
Мы говорим, что в этой ситуации таблица заказов является таблицей ссылок , а таблица продуктов — таблицей ссылок . Точно так же есть ссылочные и ссылочные столбцы.
Вы также можете сократить приведенную выше команду до:
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 . Это приводит к тому, что ссылочный столбец (столбцы) в ссылочной строке (строках) устанавливаются равными нулю или их значениям по умолчанию, соответственно, когда ссылочная строка удаляется. Обратите внимание, что это не освобождает вас от соблюдения каких-либо ограничений. Например, если действие указывает, SET DEFAULT но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция завершится ошибкой.
Аналогично тому ON DELETE , ON UPDATE который вызывается при изменении (обновлении) ссылочного столбца. Возможные действия те же. В этом случае CASCADE означает, что обновленные значения ссылочного столбца (столбцов) должны быть скопированы в ссылочную строку (строки).
Обычно ссылочная строка не должна удовлетворять ограничению внешнего ключа, если какой-либо из ее ссылочных столбцов имеет значение null. Если MATCH FULL добавляется к объявлению внешнего ключа, ссылочная строка не удовлетворяет ограничению только в том случае, если все ее ссылочные столбцы имеют значение null (поэтому сочетание нулевых и ненулевых значений гарантированно не соответствует MATCH FULL ограничению). Если вы не хотите, чтобы ссылочные строки могли избежать удовлетворения ограничения внешнего ключа, объявите ссылочный столбец (столбцы) как NOT NULL .
Внешний ключ должен ссылаться на столбцы, которые либо являются первичным ключом, либо образуют уникальное ограничение. Это означает, что столбцы, на которые ссылаются, всегда имеют индекс (тот, который лежит в основе первичного ключа или ограничения уникальности); таким образом, проверка того, имеет ли ссылочная строка совпадение, будет эффективной. Поскольку для DELETE строки из ссылочной таблицы или UPDATE ссылочного столбца потребуется сканирование ссылочной таблицы на наличие строк, соответствующих старому значению, часто рекомендуется также индексировать ссылочные столбцы. Поскольку это не всегда необходимо, и существует множество вариантов индексации, объявление ограничения внешнего ключа не создает автоматически индекс для ссылающихся столбцов.
Ограничения исключения
Ограничения исключения гарантируют, что при сравнении любых двух строк в указанных столбцах или выражениях с использованием указанных операторов хотя бы одно из этих сравнений операторов вернет значение false или null. Синтаксис:
CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) ); Добавление ограничения исключения автоматически создаст индекс типа, указанного в объявлении ограничения.
Предложение CONSTRAINT
Таблицы ограничение аналогичны индекс, однако их также можно использовать для создания связи отношение другой таблицей.
Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания и удаления ограничений. Существует два типа предложений CONSTRAINT: для создания ограничения на одно поле и на несколько полей.
Примечание: Ядро СУБД Microsoft Access не поддерживает использование CONSTRAINT или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого применяйте методы Create DAO.
Синтаксис
Ограничение на одно поле:
CONSTRAINT имя REFERENCES внешняя_таблица [(внешнее_поле1, внешнее_поле2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Ограничение на несколько полей:
CONSTRAINT имя
первичный_ключ1[, первичный_ключ2 [, . ]]) |
UNIQUE (уникальный_ключ1[, уникальный_ключ2 [, . ]]) |
NOT NULL (непустое1[, непустое2 [, . ]]) |
FOREIGN KEY [NO INDEX] (ссылка1[, ссылка2 [, . ]]) REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, . ]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT включает в себя следующие элементы:
Имя создаваемого ограничения.
первичный_ключ1, первичный_ключ2
Имена полей, определяемых как первичный ключ.
уникальный_ключ1, уникальный_ключ2
Имена полей, определяемых как уникальный ключ.
непустое1, непустое2
Имена полей, в которых не может быть значений NULL.
ссылка1, ссылка2
Имя поля или полей внешнего ключа, которые ссылаются на поля в другой таблице.
внешняя_таблица
Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.
внешнее_поле1, внешнее_поле2
Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.
Замечания
Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.
Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.
В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:
- Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- Зарезервированные слова PRIMARY KEY используются для назначения одного поля или совокупности полей таблицы в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и отличными от NULL, причем в таблице может быть только один первичный ключ.
Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))
Пример определения для таблицы Orders, в котором задается отношение внешнего ключа, ссылающееся на первичный ключ таблицы Customers:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
Для внешнего ключа определены предложения ON UPDATE CASCADE и ON DELETE CASCADE. Предложение ON UPDATE CASCADE означает, что при обновлении в таблице Customers идентификатора клиента (CustId) будет выполнено каскадное обновление в таблице Orders. В каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически внесено новое значение. Предложение ON DELETE CASCADE означает, что при удалении клиента из таблицы Customers все строки таблицы Orders, содержащие его идентификатор, также будут удалены.
Другое определение для таблицы Orders, в котором вместо действия CASCADE используется SET NULL:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Предложение ON UPDATE SET NULL означает, что при обновлении в таблице Customers идентификатора клиента (CustId) соответствующие значения внешнего ключа в таблице Orders автоматически изменятся на NULL. Аналогично предложение ON DELETE SET NULL означает, что при удалении клиента из таблицы Customers все соответствующие внешние ключи в таблице Orders принимают значение NULL.
Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
Constraint postgresql что это
Типы данных сами по себе ограничивают множество данных, которые можно сохранить в таблице. Однако для многих приложений такие ограничения слишком грубые. Например, столбец, содержащий цену продукта, должен, вероятно, принимать только положительные значения. Но такого стандартного типа данных нет. Возможно, вы также захотите ограничить данные столбца по отношению к другим столбцам или строкам. Например, в таблице с информацией о товаре должна быть только одна строка с определённым кодом товара.
Для решения подобных задач SQL позволяет вам определять ограничения для столбцов и таблиц. Ограничения дают вам возможность управлять данными в таблицах так, как вы захотите. Если пользователь попытается сохранить в столбце значение, нарушающее ограничения, возникнет ошибка. Ограничения будут действовать, даже если это значение по умолчанию.
5.3.1. Ограничения-проверки
Ограничение-проверка — наиболее общий тип ограничений. В его определении вы можете указать, что значение данного столбца должно удовлетворять логическому выражению (проверке истинности). Например, цену товара можно ограничить положительными значениями так:
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) );
Первые два ограничения определяются похожим образом, но для третьего используется новый синтаксис. Оно не связано с определённым столбцом, а представлено отдельным элементом в списке. Определения столбцов и такие определения ограничений можно переставлять в произвольном порядке.
Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя Postgres Pro этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:
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, описанное в следующем разделе.
5.3.2. Ограничения NOT NULL
Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );
Ограничение NOT NULL всегда записывается как ограничение столбца и функционально эквивалентно ограничению CHECK ( имя_столбца IS NOT NULL) , но в Postgres Pro явное ограничение 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 и использовать его в переносимых приложениях не следует. (Оно было добавлено в Postgres Pro только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:
CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );
и затем вставить ключевое слово NOT , где потребуется.
Подсказка
При проектировании баз данных чаще всего большинство столбцов должны быть помечены как NOT NULL.
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 );
При добавлении ограничения уникальности будет автоматически создан уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в ограничении. Условие уникальности, распространяющееся только на некоторые строки, нельзя записать в виде ограничения уникальности, однако такое условие можно установить, создав уникальный частичный индекс.
Вообще говоря, ограничение уникальности нарушается, если в таблице оказывается несколько строк, у которых совпадают значения всех столбцов, включённых в ограничение. Однако два значения NULL при сравнении никогда не считаются равными. Это означает, что даже при наличии ограничения уникальности в таблице можно сохранить строки с дублирующимися значениями, если они содержат NULL в одном или нескольких столбцах ограничения. Это поведение соответствует стандарту 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) );
При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL .
Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В Postgres Pro такого жёсткого требования нет, но обычно лучше ему следовать.
Первичные ключи полезны и для документирования, и для клиентских приложений. Например, графическому приложению с возможностями редактирования содержимого таблицы, вероятно, потребуется знать первичный ключ таблицы, чтобы однозначно идентифицировать её строки. Первичные ключи находят и другое применение в СУБД; в частности, первичный ключ в таблице определяет целевые столбцы по умолчанию для сторонних ключей, ссылающихся на эту таблицу.
5.3.5. Внешние ключи
Ограничение внешнего ключа указывает, что значения столбца (или группы столбцов) должны соответствовать значениям в некоторой строке другой таблицы. Это называется ссылочной целостностью двух связанных таблиц.
Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:
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 (и не равным NULL), будет невозможно.
В такой схеме таблицу 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 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 существует ON UPDATE , которое срабатывает при изменении заданного столбца. При этом возможные действия те же, а CASCADE в данном случае означает, что изменённые значения связанных столбцов будут скопированы в зависимые строки.
Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если один из связанных столбцов содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL , строка будет удовлетворять ограничению, только если все связанные столбцы равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL ). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные столбцы как NOT NULL .
Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных столбцов всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно. Так как команды DELETE для строк главной таблицы или UPDATE для зависимых столбцов потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых столбцов. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным столбцам.
Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE .
5.3.6. Ограничения-исключения
Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным столбцам или выражениям с помощью заданных операторов, минимум одно из этих сравнений возвратит false или NULL. Записывается это так:
CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );
При добавлении ограничения-исключения будет автоматически создан индекс того типа, который указан в объявлении ограничения.
| Пред. | Наверх | След. |
| 5.2. Значения по умолчанию | Начало | 5.4. Системные столбцы |