Оператор SQL FOREIGN KEY - внешний ключ

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

Когда одно поле в таблице ссылается на другое, оно называется внешним ключом, а поле на которое оно (внешний ключ) ссылается, называется родительским ключом.

Это помогает сохранять согласованность базы данных путем обеспечения так называемой «ссылочной целостности» (referential integrity).

Общий синтаксис установки внешнего ключа:

[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец_1, столбец_2, ... столбец_3)
REFERENCES главная_таблица (столбец_главной_таблицы_1, столбец_главной_таблицы_2, ... столбец_главной_таблицы_3)
[ON DELETE действие]
[ON UPDATE действие]

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представлять внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы. Затем в скобках имя связанного столбца, на который будет указывать внешний ключ.

Сценарии использования внешнего ключа

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:

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

Добавить внешний ключ при создании таблицы

Для того, чтобы привязать к таблице внешний ключ сразу при создании таблицы, необходимо выполнить запросы такого формата (на примере MySQL):

Таблица classes:

CREATE TABLE classes (
  `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(11) NOT NULL
);

Таблица student:

CREATE TABLE student (
  `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `first_name` VARCHAR(255),
  `middle_name` VARCHAR(255),
  `last_name` VARCHAR(255),
  `birthday` DATE,
  `address` VARCHAR(255)
);

Таблица student_in_class:

CREATE TABLE student_in_class (
  `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `class` INT(11) NOT NULL,
  `student` INT(11) NOT NULL,
  CONSTRAINT student_in_class_classes_fk FOREIGN KEY (`class`) REFERENCES classes(`id`) ON DELETE CASCADE,
  CONSTRAINT student_in_class_student_fk FOREIGN KEY (`student`) REFERENCES student(`id`) ON DELETE CASCADE
);

Добавить внешний ключ с помощью ALTER TABLE

Если необходимо определить поле уже созданной таблицы в качестве внешнего ключа (добавить внешний ключ), то можно воспользоваться командой ALTER TABLE и создать внешний ключ в таблице командой:

ALTER TABLE student_in_class
  ADD CONSTRAINT student_in_class_classes_fk FOREIGN KEY (`class`) REFERENCES classes(`id`);

Удалить внешний ключ

Чтобы удалить связь между таблицами, нужно использовать оператор ALTER TABLE и удалить внешний ключ, который связывает таблицы.

ALTER TABLE student_in_class DROP FOREIGN KEY student_in_class_classes_fk;