В этой статье вы узнаете, что такое ссылочная целостность в SQL и как она может быть нарушена. Мы разберем способы обновления данных в одной таблице при удалении связанных с ними записями в другой. Рассмотрим такие конструкции SQL, как ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT и ON DELETE NO ACTION. В статье будет использоваться синтаксис СУБД MySQL.
Ссылочная целостность
Выполнение каких-либо операций над связанными таблицами базы данных вызывает необходимость контролировать поведение связываемых данных. Например, при удалении какой-либо категории одной таблицы связанные с этой категорией данные таблицы-потомка должны быть обновлены определенным образом. Если этого не сделать, в таблице-потомке останутся записи, ссылающиеся на уже несуществующую категорию таблицы-предка. Подобная ситуация характеризуется как нарушение ссылочной целостности.
Ссылочная целостность — это механизм, который гарантирует согласованность данных таблиц, связанных внешними и первичными ключами. Если бы не было ссылочной целостности, то могли бы возникнуть проблемы с некорректными данными, устаревшими ссылками и другими аномалиями в базе данных. Поэтому поддержание ссылочной целостности очень важно для сохранения качества данных в реляционных базах данных.
Приведем пример нарушения ссылочной целостности. Создадим две таблицы в базе данных. В одной таблице (breeds) будем хранить информацию о породах котов: идентификатор породы, ее название, страна происхождения и описание. Вторая таблица (cats) будет содержать данные о конкретных котах: идентификатор кота, имя, возраст, а также идентификатор породы из первой таблицы.
create table breeds (
id int auto_increment,
name varchar(100),
origin varchar(50),
description text,
primary key (id)
);
create table cats (
id int auto_increment,
name varchar(100),
age int,
breed_id int,
primary key (id)
); Схематически эти таблицы будут выглядеть так:
Добавим в таблицы несколько записей, указав в колонке breed_id таблицы cats идентификаторы пород из таблицы breeds.
insert into breeds (name, origin, description)
values
('Maine Coon', 'United States', 'The Maine Coon is a large and sociable cat breed known for its friendly nature and tufted ears.'),
('Siamese', 'Thailand', 'The Siamese cat is known for its striking blue almond-shaped eyes and short coat with color points on the ears, face, paws, and tail.'),
('Persian', 'Iran', 'The Persian cat is characterized by its long and luxurious coat, short muzzle, and sweet personality.');
insert into cats (name, age, breed_id)
values
('Max', 3, 1),
('Luna', 2, 2),
('Oliver', 4, 3),
('Mia', 1, 2),
('Sophie', 2, 3); Допустим, мы не хотим хранить в своей базе данных котов с сиамской породой (Siamese). Мы производим удаление этой породы:
delete from breeds where id = 2;
В итоге запись с идентификатором 2 удалится из таблицы breeds, а в таблице cats останется запись о коте Luna с идентификатором породы, которой уже не существует в базе. Таким образом, удаление породы привело к нарушению ссылочной целостности.
Чтобы решить эту проблему, можно удалить также записи в таблице cats с идентификатором удаленной породы. Однако гораздо удобнее воспользоваться возможностями базы данных. О том, как это сделать, поговорим в следующем разделе.
Удаление записей связанных таблиц
Чтобы влиять на способ изменения данных в таблице-потомке при удалении записи из таблицы-предка, сперва необходимо связать эти таблицы. Это делается с помощью механизма внешнего ключа.
Схематически таблицы, связанные с помощью внешнего ключа, можно изобразить так:
Подробно о типах связей таблиц и внешних ключах было рассказано в этой статье.
При удалении данных из таблицы-предка нам могут понадобиться различные варианты обновления записей в таблице-потомке:
- удалить все связанные записи;
- установить NULL для записей в колонке внешнего ключа;
- запретить удаление, вернув ошибку.
Для достижения вышеперечисленных результатов в SQL есть специальные конструкции, которые можно опционально добавлять при создании внешнего ключа. Рассмотрим работу этих конструкций, используя таблицы cats и breeds из первого раздела статьи.
ON DELETE CASCADE
При использовании конструкции ON DELETE CASCADE удаление записей таблицы-предка повлечет за собой удаление связанных записей таблицы-потомка.
Синтаксис добавления конструкции ON DELETE CASCADE при создании внешнего ключа у существующей таблицы:
ALTER TABLE table_ancestor ADD FOREIGN KEY (key_name) REFERENCES table_descendant(table_descendant_column) ON DELETE CASCADE;
Продемонстрируем работу конструкции на примере. Изначально у нас есть две таблицы без внешних ключей с заполненными данными.
Таблица breeds:
| id | name | origin | description |
|---|---|---|---|
| 1 | Maine Coon | United States | The Maine Coon is a large and sociable cat breed known for its friendly nature and tufted ears. |
| 2 | Siamese | Thailand | The Siamese cat is known for its striking blue almond-shaped eyes and short coat with color points on the ears, face, paws, and tail. |
| 3 | Persian | Iran | The Persian cat is characterized by its long and luxurious coat, short muzzle, and sweet personality. |
Таблица cats:
| id | name | age | breed_id |
|---|---|---|---|
| 1 | Max | 3 | 1 |
| 2 | Luna | 2 | 2 |
| 3 | Oliver | 4 | 3 |
| 4 | Mia | 1 | 2 |
| 5 | Sophie | 2 | 3 |
Добавим внешний ключ с конструкцией в таблицу breeds, связав ее записи с записями таблицы cats:
alter table cats add foreign key (breed_id) references breeds(id) on delete cascade;
Теперь произведем удаление породы Siamese (id = 2) из таблицы breeds:
delete from breeds where id = 2;
В итоге удалится запись с id = 2 в таблице breeds и связанные с ней записи с breed_id = 2 из таблицы cats.
Таблица cats после удаления породы Siamese:
| id | name | age | breed_id |
|---|---|---|---|
| 1 | Max | 3 | 1 |
| 3 | Oliver | 4 | 3 |
| 5 | Sophie | 2 | 3 |
ON DELETE SET NULL
Конструкция ON DELETE SET NULL устанавливает в ячейку с внешним ключом значение NULL у таблицы-потомка при удалении строки из таблицы-предка.
Синтаксис добавления конструкции ON DELETE SET NULL при создании внешнего ключа у существующей таблицы выглядит следующим образом:
ALTER TABLE table_ancestor ADD FOREIGN KEY (key_name) REFERENCES table_descendant(table_descendant_column) ON DELETE SET NULL;
Для демонстрации возьмем таблицы нашего примера с изначально установленными значениями без внешних ключей.
Добавим внешний ключ с конструкцией ON DELETE SET NULL в таблицу breeds:
alter table cats add foreign key (breed_id) references breeds(id) on delete set null;
Удалим породу Siamese из таблицы breeds:
delete from breeds where id = 2;
Удаление породы Siamese приведет к установке значений NULL в колонке внешнего ключа breed_id таблицы cats у котов сиамской породы.
Таблица cats после удаления породы Siamese:
| id | name | age | breed_id |
|---|---|---|---|
| 1 | Max | 3 | 1 |
| 2 | Luna | 2 | NULL |
| 3 | Oliver | 4 | 3 |
| 4 | Mia | 1 | NULL |
| 5 | Sophie | 2 | 3 |
ON DELETE RESTRICT
Если использовать конструкцию ON DELETE RESTRICT, то при попытке удаления строки из таблицы-предка, имеющей связанные данные в таблице-потомке, будет возвращена ошибка.
Синтаксис добавления конструкции ON DELETE RESTRICT при создании внешнего ключа у существующей таблицы такой:
ALTER TABLE table_ancestor ADD FOREIGN KEY (key_name) REFERENCES table_descendant(table_descendant_column) ON DELETE RESTRICT;
Возьмем таблицы breed и cats со стартовыми значениями и создадим внешний ключ с конструкцией ON DELETE RESTRICT для таблицы breeds:
alter table cats add foreign key (breed_id) references breeds(id) on delete restrict;
Попытаемся удалить запись о породе Siamese из таблицы breeds:
delete from breeds where id = 2;
Получим ошибку:
Cannot delete or update a parent row: a foreign key constraint fails (`examples`.`cats`, CONSTRAINT `cats_ibfk_1` FOREIGN KEY (`breed_id`) REFERENCES `breeds` (`id`) ON DELETE RESTRICT)
ON DELETE NO ACTION
В MySQL конструкция ON DELETE NO ACTION является конструкцией по умолчанию. Если при создании внешнего ключа никакая из конструкций ON DELETE не добавляется, то по умолчанию устанавливается NO ACTION. Механизм обработки удаления записи таблицы-предка в таком случае будет таким, как и при использовании ON DELETE RESTRICT. То есть если есть связанные данные в таблице-потомке, то вернется ошибка, иначе запись успешно удалится.
В SQL также существует конструкция
ON UPDATE. Она устанавливает правила обновления данных таблицы-потомка при обновлении записей таблицы-предка. Конструкция имеет такие же вариации, как иON DELETE. При ее использовании связанные данные в таблице-потомке могут обновляться каскадно (ON UPDATE CASCADE), то есть при изменении идентификатора у таблицы-предка изменятся идентификаторы связанных записей таблицы-потомка. Также идентификаторы могут устанавливаться вNULL(ON UPDATE SET NULL) и выдаваться ошибка при попытке обновления записи в таблице-предке (ON UPDATE RESTRICTилиON UPDATE NO ACTION).
Заключение
Таким образом, для сохранения ссылочной целостности при удалении записей в SQL существуют специальный механизм. База данных автоматически обновляет записи в одной таблице при удалении связанной с ними записями в другой. Для активации такого механизма необходимо создать связь между таблицами через внешний ключ с указанием соответствующего способа обработки связанных данных в таблице-потомке. Результатом операции удаления записи в таблице-предке может быть удаление связанных записей в таблице-потомке, установка значения NULL у этих записей, а также возврат ошибки.