Внешний ключ таблицы

Внешний ключ строится в дочерней (зависимой) таблице для соединения родительской (главной) и дочерних таблиц БД.

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

Таблицу, в которой определен внешний ключ, будем называть зависимой, а таблицу с первичным ключом — главной. Ссылочная целостность данных двух таблиц обеспечивается следующим образом: в зависимую таблицу нельзя вставить строку, если внешний ключ не имеет соответствующего значения в главной таблице, а из главной таблицы нельзя удалить строку, если значение первичного ключа используется в зависимой таблице.

Например, если строка наименования дисциплины удалена из таблицы «Дисциплины», а идентификатор этой дисциплины (ID_Дисциплина) используется в таблице «Учебный план», то относительная целостность между этими двумя таблицами будет нарушена — строки таблицы «Учебный план» с удаленным идентификатором останутся «осиротевшими». Ограничение FOREIGN KEY предотвращает возникновение подобных ситуаций — удаление строки первичного ключа не состоится.

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

Синтаксис определения внешнего ключа следующий:

FOREIGN KEY (< список столбцов внешнего ключа>)

REFERENCES < имя родительской таблицы>

[[< список столбцов родительской таблицы> ]

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

[0N UPDATE {NO ACTION CASCADE SET DEFAULTS SET NULL}]

Список столбцов внешнего ключа определяет столбцы дочерней таблицы, по которым строится внешний ключ.

Имя родительской таблицы определяет таблицу, в которой описан первичный ключ (или столбец с атрибутом UNIQUE). На этот ключ (столбец) должен ссылаться внешний ключ дочерней таблицы для обеспечения ссылочной целостности. Список столбцов родительской таблицы, определяющий ссылочную целостность, необязателен при ссылке на первичный ключ родительской таблицы. При ссылке в родительской таблице на столбец с атрибутом UNIQUE этот список лучше привести.

Параметры ON DELETE, ON UPDATE задают способы изменения подчиненных записей дочерней таблицы при удалении (ON DELETE) или изменении (ON UPDATE) поля связи в записи родительской таблицы. Перечислим эти способы:

• NO ACTION — запрещает удаление изменение родительской записи при наличии подчиненных записей в дочерней таблице;

• CASCADE — при удалении записи родительской таблицы (используется совместно с ON DELETE) происходит удаление всех подчиненных записей в дочерней таблице; при изменении поля связи в записи родительской таблицы (используется совместно с ON UPDATE) происходит изменение на то же значение поля внешнего ключа у всех подчиненных записей в дочерней таблице;

• SET DEFAULT — в поле внешнего ключа записей дочерней таблицы заносится значение этого поля по умолчанию, указанное при определении поля (параметр DEFAULT);

• SET NULL — в поле внешнего ключа записей дочерней таблицы заносится значение NULL.

Установим связь между таблицами «Студенты», «Учебный_план» и «Сводная_ведомость».

ALTER 'ТАВОТЕ Сводная_ведомость

ADD FOREIGN KEY(ID_План)

REFERENCES Учебный_план

ALTER ТАВLЕ Сводная_ведомость

ADD FOREIGN KEY (ID_Студент)

REFERENCES Студенты

Хотя в рассмотренном примере имена столбцов первичного и внешнего ключей в обеих таблицах совпадают, это не является обязательным. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено ограничение FOREIGN KEY, может иметь совершенно другое имя. Однако лучше давать таким столбцам идентичные названия, чтобы показать связь между ними (рис. 7.3).