Л. Партыка, И. И. Попов системы управления базами данных

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

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

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

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

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

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

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

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

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

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

[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT| 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 TABLE Сводная_ведомость ADD FOREIGN KEY (ID_План)

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

ALTER TABLE Сводная_ведомость ADD FOREIGN KEY (ID_Студент) REFERENCES Студенты

Рис. 4.3. Связь внешнего и первичного ключей

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