Базы данных. Вводный курс

Сергей Кузнецов

Назад Содержание Вперёд

16.2.2. Определение табличного ограничения

Элемент определения табличного ограничения целостности задается в следующем синтаксисе:

base_table_constraint_definition ::= 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE } ( column_commalist )
    | FOREIGN KEY ( column_commalist ) 
       references_definition
    | CHECK ( conditional_expression )

Как мы видим, имеется три разновидности табличных ограничений: ограничение первичного или возможного ключа (PRIMARY KEY или UNIQUE), ограничение внешнего ключа (FOREIGN KEY) и проверочное ограничение (CHECK). Любому ограничению может явным образом назначаться имя, если перед определением ограничения поместить конструкцию CONSTRAINT constraint_name.

Табличное ограничение первичного или возможного ключа

Табличное ограничение первичного или возможного ключа  { PRIMARY_KEY | UNIQUE } (column_commalist) означает требование уникальности составных значений указанной группы столбцов (т. е. во все время существования определяемой таблицы во всех ее строках составные значения данной группы столбцов должны быть различны⁹⁸⁾). Ограничение PRIMARY KEY, в дополнение к этому, влечет ограничение NOT NULL для всех столбцов, упоминаемых в определении ограничения. В определении таблицы допускается произвольное число определений возможного ключа (для разных комбинаций столбцов), но не более одного определения первичного ключа. Обратите особое внимание на последнюю часть предыдущего предложения: в языке SQL действительно допускается определение таблиц, у которых отсутствуют возможные ключи. Эта особенность языка, среди прочего, очевидным образом противоречит базовым требованиям реляционной модели данных.

Проверочное табличное ограничение

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

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

Табличное ограничение внешнего ключа

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

Табличное ограничение FOREIGN KEY (column_commalist) references_definition означает объявление внешним ключом группы столбцов, имена которых перечислены в списке column_commalist. Обсудим теперь смысл ограничения внешнего ключа при разных вариантах формирования определения ссылок (references_definition). Для удобства повторим синтаксическое правило.

references_definition ::= 
    REFERENCES base_table_name [ (column_commalist) ]
        [ MATCH { SIMPLE | FULL | PARTIAL } ]
        [ ON DELETE referential_action ]
        [ ON UPDATE referential_action ]

В этом определении base_table_name должно представлять собой имя некоторой базовой таблицы (пусть, например, эта таблица имеет имя T). Если определение ссылок включает список столбцов (column_commalist), то этот список должен совпадать (с точностью до порядка следования имен столбцов) со списком имен столбцов, использованных в некотором определении первичного или возможного ключа (PRIMARY_KEY или UNIQUE) в определении таблицы T. Если в определении ссылок список столбцов явно не задан, то считается, что он совпадает со списком столбцов, использованных в определении первичного ключа (PRIMARY_KEY) таблицы T.

Разновидности способов сопоставления значений внешнего и возможного ключей

Пусть определяемая таблица имеет имя S. Обсудим смысл необязательного раздела определения внешнего ключа MATCH { SIMPLE | FULL | PARTIAL }. Если этот раздел отсутствует или если присутствует и имеет вид MATCH SIMPLE, то ограничение внешнего ключа (ссылочное ограничение) удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

• (a) какой-либо столбец, входящий в состав внешнего ключа, содержит NULL;
• (b) таблица T содержит в точности одну строку, такую, что значение внешнего ключа в данной строке таблицы S совпадает со значением соответствующего возможного ключа в этой строке таблицы T.

Если раздел MATCH присутствует в определении внешнего ключа и имеет вид MATCH PARTIAL, то ограничение внешнего ключа удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

• (a) каждый столбец, входящий в состав внешнего ключа, содержит NULL;
• (b) таблица T содержит по крайней мере одну такую строку, что для каждого столбца данной строки таблицы S, значение которого отлично от NULL, его значение совпадает со значением соответствующего столбца возможного ключа в этой строке таблицы T.

Если раздел MATCH имеет вид MATCH FULL, то ограничение внешнего ключа удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

• (a) каждый столбец, входящий в состав внешнего ключа, содержит NULL;
• (b) ни один столбец, входящий в состав внешнего ключа, не содержит NULL, и таблица T содержит в точности одну строку, такую, что значение внешнего ключа в данной строке таблицы S совпадает со значением соответствующего возможного ключа в этой строке таблицы T.

Очевидно, что только при наличии спецификации MATCH FULL ссылочное ограничение соответствует требованиям реляционной модели. Тем не менее, в определении ограничения внешнего ключа  базовых таблиц в SQL по умолчанию предполагается наличие спецификации MATCH SIMPLE.⁹⁹⁾

Поддержка ссылочной целостности и ссылочные действия

В связи с определением ограничения внешнего ключа нам осталось рассмотреть еще два необязательных раздела – ON DELETE referential_action и ON UPDATE referential_action. Прежде всего, приведем синтаксическое правило:

referential_action ::= 
   { NO ACTION | RESTRICT | CASCADE 
    | SET DEFAULT | SET NULL }

Чтобы объяснить, в каких случаях и каким образом выполняются эти действия, требуется сначала определить понятие ссылающейся строки (referencing row). Если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE либо MATCH FULL, то для данной строки t таблицы T строкой таблицы S, ссылающейся на строку t, называется каждая строка таблицы S, значение внешнего ключа которой совпадает со значением соответствующего возможного ключа строки t. Если в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то для данной строки t таблицы T строкой таблицы S, ссылающейся на строку t, называется каждая строка таблицы S, отличные от NULL значения столбцов внешнего ключа которой совпадают со значениями соответствующих столбцов соответствующего возможного ключа строки t. В случае MATCH PARTIAL строка таблицы S называется ссылающейся исключительно на строку t таблицы T, если эта строка таблицы S является ссылающейся на строку t и не является ссылающейся на какую-либо другую строку таблицы T.¹⁰⁰⁾

Теперь приступим к ссылочным действиям. Пусть определение ограничения внешнего ключа содержит раздел ON DELETE referential_action. Предположим, что предпринимается попытка удалить строку t из таблицы T. Тогда:

• если в качестве требуемого ссылочного действия указано NO ACTION или RESTRICT, то операция удаления отвергается, если ее выполнение вызвало бы нарушение ограничения внешнего ключа;
• если в качестве требуемого ссылочного действия указано CASCADE, то строка t удаляется, и если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE или MATCH FULL, то удаляются все строки, ссылающиеся на t. Если же в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то удаляются только те строки, которые ссылаются исключительно на строку t;
• если в качестве требуемого ссылочного действия указано SET DEFAULT, то строка t удаляется, и во всех столбцах, которые входят в состав внешнего ключа, всех строк, ссылающихся на строку t, проставляется заданное при их определении значение по умолчанию. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t;
• если в качестве требуемого ссылочного действия указано SET NULL, то строка t удаляется, и во всех столбцах, которые входят в состав внешнего ключа, всех строк, ссылающихся на строку t, проставляется NULL. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t.

Пусть определение ограничения внешнего ключа содержит раздел ON UPDATE referential_action. Предположим, что предпринимается попытка обновить столбцы соответствующего возможного ключа в строке t из таблицы T. Тогда:

• если в качестве требуемого ссылочного действия указано NO ACTION или RESTRICT, то операция обновления отвергается, если ее выполнение вызвало бы нарушение ограничения внешнего ключа;
• если в качестве требуемого ссылочного действия указано СASCADE, то строка t обновляется, и если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE или MATCH FULL, то соответствующим образом обновляются все строки, ссылающиеся на t (в них должным образом изменяются значения столбцов, входящих в состав внешнего ключа). Если же в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то обновляются только те строки, которые ссылаются исключительно на строку t;
• если в качестве требуемого ссылочного действия указано SET DEFAULT, то строка t обновляется, и во всех столбцах, которые входят в состав внешнего ключа и соответствуют изменяемым столбцам таблицы T, всех строк, ссылающихся на строку t, проставляется заданное при их определении значение по умолчанию. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t, причем в них изменяются значения только тех столбцов, которые не содержали NULL;
• если в качестве требуемого ссылочного действия указано SET NULL, то строка t обновляется, и во всех столбцах, которые входят в состав внешнего ключа и соответствуют изменяемым столбцам таблицы T, всех строк, ссылающихся на строку t, проставляется NULL. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t.¹⁰¹⁾

---

98   С учетом замечания по поводу особого толкования семантики неопределенных значений, сделанного в предыдущей сноске.

99   Если определяется внешний ключ, состоящий из одного столбца, то явное указание спецификации MATCH любой разновидности становится бессмысленным, поскольку в этом случае MATCH SIMPLE, MATCH PARTIAL и MATCH FULL ведут себя одинаково.

100   Из приведенных ранее объяснений действия ограничения внешнего ключа при наличии в определении внешнего ключа раздела MATCH PARTIAL ясно следует, что в этом случае одна строка таблицы S может являться ссылающейся на несколько разных строк таблицы T.

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

Назад Содержание Вперёд