Ограничение ссылочной целостности (FOREIGN KEY)

Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной БД. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY, применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом. Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES. Данные в столбцах, определенных в качестве внешнего ключа, могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Единственным требованием остается соответствие столбцов по типу и размеру данных.

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

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

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

Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION, принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE.

Синтаксис:

[ FOREIGN KEY ]

REFERENCES referenced_table_name [ (ref_column [,... ]) ]

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT} ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

Где:

referenced_table_name — имя таблицы, на которую ссылается ограничение FOREIGN KEY.

(ref_column [,... ]) — с толбец или список столбцов из таблицы, на которую ссылается ограничение FOREIGN KEY. Можно не указывать, если ссылка идет на первичный ключ.

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } — определяет операцию, которая производится над строками создаваемой таблицы, если эти строки имеют ссылочную связь, а строка, на которую имеются ссылки, удаляется из родительской таблицы.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } — указывает, какое действие совершается над строками в изменяемой таблице, когда у этих строк есть ссылочная связь и строка родительской таблицы, на которую указывает ссылка, обновляется.

Параметр по умолчанию — NO ACTION.

NO ACTION — ядро СУБД формирует ошибку, и выполняется откат операции удаления (обновления) строки из родительской таблицы.

CASCADE — если из(в) родительской таблицы удаляется (обновляется) строка, соответствующие ей строки удаляются(обновляются) из(в) ссылающейся таблицы.

SET NULL — все значения, составляющие внешний ключ, при удалении (обновлении) соответствующей строки родительской таблицы устанавливаются в NULL. Для выполнения этого ограничения столбцы внешних ключей должны допускать существование значений NULL.

SET DEFAULT — все значения, составляющие внешний ключ, при удалении (обновлении) соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для выполнения этого ограничения все столбцы внешних ключей должны иметь определения по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию для данного столбца.

Параметр ON DELETE CASCADE нельзя указывать, если в таблице уже существует триггер ON DELETE.

Действие ON UPDATE CASCADE не может быть определено, если в изменяемой таблице уже существует триггер INSTEAD OF ON UPDATE.

Пример использования.

Внешний ключ — ограничение на столбец.

CREATE ТABLE Состав (

Блюдо INT NOT NULL FOREIGN KEY REFERENCES Блюда(ID_Блюда),

Продукт INT NOT NULL REFERENCES Продукты,

Вес INT,

PRIMARY KEY ( Блюдо, Продукт ));

Внешний ключ — ограничение на таблицу.

CREATE ТABLE Заказы (

ID_Заказ INT NOT NULL,

Блюдо INT NOT NULL,

Количество_порций INT NOT NULL CHECK( Количество_порций >0 ),

Дата DATE NOT NULL,

PRIMARY KEY( ID_Заказ, Блюдо, Дата ),

FOREIGN KEY ( Блюдо, Дата ) REFERENCES Меню ( Блюдо, Дата ));

Рекурсивная ссылка — внешний ключ ссылается на первичный ключ той же таблицы.

CREATE TABLE Сотрудник(

[Табельный номер ] INT NOT NULL PRIMARY KEY,

[серия паспорта] INT,

[номер паспорта] INT,

[дата выдачи] DATETIM E,

ФИО VARCHAR(25) NOT NULL,

Адрес VARCHAR(100),

UNIQUE ([серия паспорта], [номер паспорта]));

Для столбца Руководитель нельзя использовать ограничение NOT NULL, так как ни одну запись нельзя будет добавить в таблицу.