Constraint postgresql что это

PostgreSQL уникальные ограничения

В этом учебном пособии вы узнаете, как создавать, добавлять и удалять уникальные ограничения в PostgreSQL с синтаксисом и примерами.

Что такое уникальное ограничение в PostgreSQL?

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

В чем разница между уникальным ограничением и первичным ключом?

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

Создать уникальное ограничение — использование оператора CREATE TABLE

Синтаксис для создания уникального ограничения с помощью оператора CREATE TABLE в PostgreSQL:

Constraint postgresql что это

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

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER )

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

Установка первичного ключа на уровне таблицы:

CREATE TABLE Customers ( Id SERIAL, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER, PRIMARY KEY(Id) );

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

CREATE TABLE OrderLines ( OrderId INTEGER, ProductId INTEGER, Quantity INTEGER, Price MONEY, PRIMARY KEY(OrderId, ProductId) );

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

UNIQUE

Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE .

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30) UNIQUE, Phone CHARACTER VARYING(30) UNIQUE, Age INTEGER );

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

Также мы можем определить этот атрибут на уровне таблицы:

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30), Phone CHARACTER VARYING(30), Age INTEGER, UNIQUE(Email, Phone) );

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30), Phone CHARACTER VARYING(30), Age INTEGER, UNIQUE(Email), UNIQUE(Phone) );

NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа — в этом случае по умолчанию столбец имеет значение NOT NULL.

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Age INTEGER );

DEFAULT

Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Age INTEGER DEFAULT 18 );

Здесь для столбца Age предусмотрено значение по умолчанию 18.

CHECK

Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Age INTEGER DEFAULT 18 CHECK(Age >0 AND Age < 100), Email CHARACTER VARYING(30) UNIQUE CHECK(Email !=''), Phone CHARACTER VARYING(20) UNIQUE CHECK(Phone !='') );

Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также с помощью CHECK можно создать ограничение в целом для таблицы:

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, Age INTEGER DEFAULT 18, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30) UNIQUE, Phone CHARACTER VARYING(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) );

Оператор CONSTRAINT. Установка имени ограничений.

С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, CHECK.

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

CREATE TABLE Customers ( Id SERIAL CONSTRAINT customer_Id PRIMARY KEY, Age INTEGER CONSTRAINT customers_age_check CHECK(Age >0 AND Age < 100), FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Email CHARACTER VARYING(30) CONSTRAINT customers_email_key UNIQUE, Phone CHARACTER VARYING(20) CONSTRAINT customers_phone_key UNIQUE );

В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.

И также можно задать все имена ограничений через атрибуты таблицы:

CREATE TABLE Customers ( Id SERIAL, Age INTEGER, FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Email CHARACTER VARYING(30), Phone CHARACTER VARYING(20), CONSTRAINT customer_Id PRIMARY KEY(Id), CONSTRAINT customers_age_check CHECK(Age >0 AND Age < 100), CONSTRAINT customers_email_key UNIQUE(Email), CONSTRAINT customers_phone_key UNIQUE(Phone) );

Вне зависимости от того, используется оператор CONSTRAINT для создания ограничений или нет (в этом случае при установке ограничений PostgreSQL сам дает им имена), мы можем просмотреть все ограничения в pgAdmin в узле базы данных в подузле :

Constraint postgresql что это

В каталоге pg_constraint хранятся ограничения-проверки, ограничения-исключения, а также ограничения первичного ключа, уникальности и внешних ключей, определённые для таблиц. (Ограничения столбцов описываются как и все остальные. Любое ограничение столбца равнозначно некоторому ограничению таблицы.) Ограничения на NULL представляются не здесь, а в каталоге pg_attribute .

Для пользовательских триггеров ограничений (создаваемых командой CREATE CONSTRAINT TRIGGER ) в этой таблице также создаётся запись.

Здесь также хранятся ограничения доменов.

Таблица 49.13. Столбцы pg_constraint

Имя	Тип	Ссылки	Описание
oid	oid	Идентификатор строки (скрытый атрибут; должен выбираться явно)
conname	name	Имя ограничения (не обязательно уникальное!)
connamespace	oid	pg_namespace .oid	OID пространства имён, содержащего это ограничение
contype	char	c = ограничение-проверка (check), f = внешний ключ (foreign key), p = первичный ключ (primary key), u = ограничение уникальности (unique), t = триггер ограничения (trigger), x = ограничение-исключение (exclusion)
condeferrable	bool	Является ли ограничение откладываемым?
condeferred	bool	Является ли ограничение отложенным по умолчанию?
convalidated	bool	Было ли ограничение проверено? В настоящее время значение false возможно только для внешних ключей и ограничений CHECK
conrelid	oid	pg_class .oid	Таблица, для которой установлено это ограничение; 0, если это не ограничение таблицы
contypid	oid	pg_type .oid	Домен, к которому относится это ограничение; 0, если это не ограничение домена
conindid	oid	pg_class .oid	Индекс, поддерживающий это ограничение, если это ограничение уникальности, первичного или внешнего ключа, либо ограничение-исключение; в противном случае — 0
confrelid	oid	pg_class .oid	Если это внешний ключ, таблица, на которую он ссылается; иначе 0
confupdtype	char	Код действия при изменении внешнего ключа: a = нет действия, r = ограничить (restrict), c = каскадное действие (cascade), n = присвоить NULL, d = поведение по умолчанию
confdeltype	char	Код действия при удалении внешнего ключа: a = нет действия, r = ограничить (restrict), c = каскадное действие (cascade), n = присвоить NULL, d = поведение по умолчанию
confmatchtype	char	Тип сопоставления внешнего ключа: f = полное (full), p = частичное (partial), s = простое (simple)
conislocal	bool	Ограничение определено локально в данном отношении. Заметьте, что ограничение может быть определено локально и при этом наследоваться.
coninhcount	int4	Число прямых предков этого ограничения. Ограничение с ненулевым числом предков нельзя удалить или переименовать.
connoinherit	bool	Ограничение определено локально для данного отношения и является ненаследуемым.
conkey	int2[]	pg_attribute .attnum	Для ограничений таблицы (включая внешние ключи, но не триггеры ограничений), определяет список столбцов, образующих ограничение
confkey	int2[]	pg_attribute .attnum	Для внешнего ключа определяет список столбцов, на которые он ссылается
conpfeqop	oid[]	pg_operator .oid	Для внешнего ключа — список операторов равенства для сравнений PK = FK
conppeqop	oid[]	pg_operator .oid	Для внешнего ключа — список операторов равенства для сравнений PK = PK
conffeqop	oid[]	pg_operator .oid	Для внешнего ключа — список операторов равенства для сравнений FK = FK
conexclop	oid[]	pg_operator .oid	Для ограничения-исключения — список операторов исключения по столбцам
conbin	pg_node_tree	Для ограничения-проверки — внутреннее представление выражения
consrc	text	Для ограничения-проверки — понятное человеку представление выражения

В случае с ограничением-исключением значение conkey полезно только для элементов ограничений, представляющих простые ссылки на столбцы. Для других случаев в conkey задаётся ноль, и чтобы получить выражение, определяющее ограничение, надо обратиться к соответствующему индексу. (Таким образом, поле conkey имеет то же содержимое, что и pg_index . indkey для индекса.)

Примечание

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

Примечание

Поле pg_class.relchecks должно согласовываться с числом ограничений-проверок, описанных в данной таблице для каждого отношения.

Пред.	Наверх	След.
49.12. pg_collation	Начало	49.14. pg_conversion

Constraint postgresql что это

SET CONSTRAINTS — установить время проверки ограничений для текущей транзакции

Синтаксис

SET CONSTRAINTS < ALL | имя [, . ] >

Описание

SET CONSTRAINTS определяет, когда будут проверяться ограничения в текущей транзакции. Ограничения IMMEDIATE проверяются в конце каждого оператора, а ограничения DEFERRED откладываются до фиксации транзакции. Режим IMMEDIATE или DEFERRED задаётся для каждого ограничения независимо.

При создании ограничение получает одну из следующих характеристик: DEFERRABLE INITIALLY DEFERRED (откладываемое, изначально отложенное), DEFERRABLE INITIALLY IMMEDIATE (откладываемое, изначально немедленное) или NOT DEFERRABLE (неоткладываемое). Третий вариант всегда подразумевает IMMEDIATE и на него команда SET CONSTRAINTS не влияет. Первые два варианта запускаются в каждой транзакции в указанном режиме, но их поведение можно изменить в рамках транзакции командой SET CONSTRAINTS .

SET CONSTRAINTS со списком имён ограничений меняет режим только этих ограничений (все они должны быть откладываемыми). Имя любого ограничения можно дополнить схемой. Если имя схемы не указано, в поисках первого подходящего имени будет просматриваться текущий путь поиска схем. SET CONSTRAINTS ALL меняет режим всех откладываемых ограничений.

Когда SET CONSTRAINTS меняет режим ограничения с DEFERRED на IMMEDIATE , новый режим начинает действовать в обратную сторону: все изменения данных, ожидающие проверки в конце транзакции, вместо этого проверяются в момент выполнения команды SET CONSTRAINTS . Если какое-либо ограничение нарушается, при выполнении SET CONSTRAINTS происходит ошибка (и режим проверки не меняется). Таким образом, с помощью SET CONSTRAINTS можно принудительно проверить ограничения в определённом месте транзакции.

В настоящее время это распространяется только на ограничения UNIQUE , PRIMARY KEY , REFERENCES (внешний ключ) и EXCLUDE . Ограничения NOT NULL и CHECK всегда проверяются немедленно в момент добавления или изменения строки ( не в конце оператора). Ограничения уникальности и ограничения-исключения, объявленные без указания DEFERRABLE , так же проверяются немедленно.

Срабатывание триггеров, объявленных как « триггеры ограничений » так же зависит от этой команды — они срабатывают в момент, когда должно проверяться соответствующее ограничение.

Замечания

Так как Postgres Pro не требует, чтобы имена ограничений были уникальны в схеме (достаточно уникальности в таблице), возможно, что для заданного имени найдётся несколько соответствующих ограничений. В этом случае SET CONSTRAINTS подействует на все эти ограничения. Для имён без указания схемы, её действие будет распространяться только на ограничение(я), найденное в первой из схем; другие схемы просматриваться не будут.

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

Совместимость

Эта команда реализует поведение, описанное в стандарте SQL, с одним исключением — в Postgres Pro она не влияет на проверку ограничений NOT NULL и CHECK . Кроме того, Postgres Pro проверяет неоткладываемые ограничения уникальности немедленно, а не в конце оператора, как предлагает стандарт.

Пред.	Наверх	След.
SET	Начало	SET ROLE