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

 
Для таблиц типа InnoDB предусмотрена возможность автоматического контроля над ссылочной целостностью базы данных с помощью внешних ключей. Внешний ключ указывает, что поле или комбинация полей текущей таблицы содержат ссылку на другую таблицу. Его можно добавить при создании таблицы с помощью оператора CREATE TABLE, а с помощью оператора ALTER TABLE можно добавить внешний ключ в уже существующую таблицу.

 
Добавляется внешний ключ с помощью конструкции FOREIGN KEY. Конструкция имеет следующий формат:
FOREIGN KEY [<Имя ключа>] (<Список полей в текущей таблице>)
REFERENCES <Имя внешней таблицы> (<Список полей во внешней таблице>)
[ON DELETE <Действие>]
[ON UPDATE <Действие>]

 
В параметре <Действие> могут быть указаны значения:
□ CASCADE — удаление или изменение записи, содержащей первичный ключ, приведет к автоматическому удалению или изменению соответствующих записей в таблице-потомке;
□ SET NULL — при удалении или изменении записи, содержащей первичный ключ, соответствующие записи в таблице - потомке получат значение NULL;
□ NO ACTION — при удалении или изменении записи, содержащей первичный ключ, никаких действий не производится, пока в таблице-потомке существуют ссылающиеся записи;
□ RESTRICT — нельзя удалить или изменить запись, пока в таблице-потомке существуют ссылающиеся записи.

Если действие не указано, это равносильно указанию действия NO ACTION.

Для примера создадим в базе данных tests следующие таблицы:
□ users_foreign — для хранения данных о клиентах:
CREATE TABLE `users_foreign` (
`id_user` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
□ product_foreign — для хранения данных о товарах:
CREATE TABLE `product_foreign` (
`id_product` INT NOT NULL AUTO_INCREMENT,
`name_product` VARCHAR(255),
PRIMARY KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
□ orders_foreign — для хранения сведений о покупках:
CREATE TABLE `orders_foreign` (
`id_orders` INT NOT NULL AUTO_INCREMENT,
`id_product` INT,
`id_user` INT,
`count` INT,
PRIMARY KEY (`id_orders`),
FOREIGN KEY (`id_user`) REFERENCES `users_foreign`
(`id_user`)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`id_product`)
REFERENCES `product_foreign` (`id_product`)
ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

 
Добавим в таблицы несколько записей:
INSERT INTO `users_foreign` VALUES (1, 'Иванов');
INSERT INTO `users_foreign` VALUES (2, 'Петров');
INSERT INTO `product_foreign` VALUES (1, 'Монитор');
INSERT INTO `product_foreign` VALUES (2, 'Клавиатура');
INSERT INTO `product_foreign` VALUES (3, 'Мышь');

INSERT INTO `orders_foreign` VALUES (1, 1, 1, 2);
INSERT INTO `orders_foreign` VALUES (2, 3, 2, 5);
INSERT INTO `orders_foreign` VALUES (3, 2, 1, 1);

Теперь попробуем удалить господина Иванова из таблицы users_foreign:

DELETE FROM `users_foreign` WHERE `id_user`=1;
SELECT `name` FROM `users_foreign`;
/* Выведет: Петров */
 

Посмотрим, сколько заказов осталось в таблице orders_foreign:

SELECT `id_orders` FROM `orders_foreign`;
/* Выведет: 2 */
 

Как видно из этого примера, удаление господина Иванова привело к автоматическому удалению его заказа за счет применения ключевого слова CASCADE.

 
Теперь попробуем добавить заказ на имя уже не существующего в базе данных господина Иванова:
INSERT INTO `orders_foreign` VALUES (NULL, 1, 1, 2);
В итоге получим ошибку:
#1452 - Cannot add or update a child row: a foreign key constraint fails

Попробуем удалить товар с номером 3 из таблицы product_foreign.
DELETE FROM `product_foreign` WHERE `id_product`=3;
В итоге также получим ошибку:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails

Иными словами, пока мы не удалим заказ с номером 2 из таблицы orders_foreign, мы не сможем удалить товар с номером 3 из таблицы product_foreign. Это достигается за счет применения ключевого слова RESTRICT.