Определение ограничений для таблицы

Объявление ограничений имеет в стандарте SQL92 следующее формальное описание:

table_constraint определяется как:

[ CONSTRAINT constraint_name ]
{ PRIMARY KEY (имя_поля .,…) }
| { UNIQUE (имя_поля .,…) } | { FOREIGN KEY (имя_поля .,…) }
{ REFERENCES имя_таблицы [(имя_поля .,…)] [ref_specification] }
| { CHECK (condition) }
[[ NOT ] DEFFERABLE ]

column_constraint определяется как:

[ CONSTRAINT constraint_name ]
{ NOT NULL } | { PRIMARY KEY } | UNIQUE
| { REFERENCES имя_таблицы [(имя_поля .,…) ] [ref_specification]}
| { CHECK (condition) }
| [ INITIALLY DEFFERED | INITIALLY IMMEDIATE ]
[[ NOT ] DEFFERABLE ]

ref_specification определяется как:

[ MATCH {FULL | PARTIAL } ]
[ ON UPDATE
{ CASCADE | SET NULL | SET DEFAULT | NO ACTION } ]
[ ON DELETE
{ CASCADE | SET NULL | SET DEFAULT | NO ACTION } ]

Ссылочная спецификация (ref_specification) определяет для ограничений FOREIGN KEY и REFERENCES тип совпадения и действия, выполняемые при внесении изменений в родительский ключ. Совпадение может быть определено как MATCH FULL (полное совпадение) или MATCH PARTIAL (частичное совпадение).

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

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

Если тип совпадения не указан, то предполагается что любое значение внешнего ключа присутствует в родительском ключе, но при этом во внешнем ключе допускаются значения NULL (частично или полностью).

Рассмотрим таблицу tbl1, для которой определен родительский ключ как столбцы f1 и f2, и таблицу tbl2 с внешним ключом по столбцам c1 и c2. Если таблицы tbl1 tbl2 имеют следующие значения:

то при указании фразы MATCH FULL ограничение ссылочной целостности допустит ввести во вторую таблицу только первую и третью строки (первая содержит в качестве значений внешнего ключа значение NULL, а третья- внешний ключ, полностью совпадающий с родительским). При указании фразы MATCH PARTIAL ограничение ссылочной целостности допустит ввести во вторую таблицу все указанные строки (первую строку - как допустимое несовпадающее значение, вторую и четвертую- как совпадающие с частичным значением NULL, третью- как уникально совпадающую).

Действия, выполняемые при внесении ограничений в родительский ключ, могут быть указаны фразами ON UPDATE и ON DELETE.

Фразы ON UPDATE и ON DELETE могут иметь одну из следующих опций:

• CASCADE - распространение изменений, произведенных в родительском ключе, на совпадающие строки внешнего ключа (для MATCH PARTIAL - только на уникально совпадающие строки);
• SET NULL - значения внешнего ключа изменяются на NULL по следующим правилам: для MATCH FULL - заменяются все значения внешнего ключа; для MATCH PARTIAL - в уникально совпадающих строках заменяются значения только тех столбцов, значения которых в родительском ключе были изменены; если тип совпадения не указан, то заменяются значения только тех столбцов, значения которых в родительском ключе были изменены;
• SET DEFAULT - значения внешнего ключа изменяются на значение по умолчанию по тем же правилам, что и для фразы SET NULL, но при типе соответствия MATCH FULL заменяются значения только тех столбцов внешнего ключа, которые уникально соответствуют родительскому ключу (значение NULL внешнего ключа не заменяется).

NO ACTION - никаких действий во внешнем ключе не выполняется, допускаются только изменения родительского ключа, не нарушающие ссылочную целостность.

Фраза DEFFERABLE в ссылочной спецификации указывает отсроченную проверку ограничения до конца транзакции. Фраза NOT DEFFERABLE определяет, что контроль ссылочной целостности будет выполняться при каждом операторе INSERT, DELETE и UPDATE.

Режим проверки ограничения - сразу или с отсрочкой - устанавливается в начале каждого сеанса, а затем он может быть изменен SQL-оператором SET CONSTRAINTS MODE или локально фразами DEFFERABLE и NOT DEFFERABLE.