Ссылочная целостность—это состояние реляционной базы данных в которой записи не могут ссылаться на несуществующие записи в этой базе данных. FOREIGN KEY—особый вид ограничения(constraint) MySQL, которое позволяет предотвратить нарушение ссылочной целостности при удалении/изменении информации в таблицах предках. Поддержка FOREIGN KEY поддерживается только для таблиц типа InnoDB
Пример нарушения ссылочной целостности
Пусть существуют две таблицы. Catalogs, являющаяся таблицей-предком, содержащие в себе упоминания о категориях товаров в интернет магазине и таблица products являющаяся таблицей-потомком, со всеми товарами этого магазина
mysql> SELECT * FROM catalogs;
+------------+-------------------------------------+
| id_catalog | name |
+------------+-------------------------------------+
| 1 | Процессоры |
| 2 | Материнские платы |
| 3 | Видеоадаптеры |
| 4 | Жёсткие диски |
| 5 | Оперативная память |
+------------+-------------------------------------+
mysql> SELECT * FROM products;
+------------+-------------------------------+------------+
| id_product | name | id_catalog |
+------------+-------------------------------+------------+
| 1 | Celeron 1.8 | 1 |
| 2 | Celeron 2.0GHz | 1 |
| 3 | Celeron 2.4GHz | 1 |
| 4 | Celeron D 320 2.4GHz | 1 |
| 5 | Celeron D 325 2.53GHz | 1 |
| 6 | Celeron D 315 2.26GHz | 1 |
| 7 | Intel Pentium 4 3.2GHz | 1 |
| 8 | Intel Pentium 4 3.0GHz | 1 |
| 9 | Intel Pentium 4 3.0GHz | 1 |
| 10 | Gigabyte GA-8I848P-RS | 2 |
| 11 | Gigabyte GA-8IG1000 | 2 |
| 12 | Gigabyte GA-8IPE1000G | 2 |
| 13 | Asustek P4C800-E Delux | 2 |
| 14 | Asustek P4P800-VM\L i865G | 2 |
| 15 | Epox EP-4PDA3I | 2 |
| 16 | ASUSTEK A9600XT/TD | 3 |
| 17 | ASUSTEK V9520X | 3 |
| 18 | SAPPHIRE 256MB RADEON 9550 | 3 |
| 19 | GIGABYTE AGP GV-N59X128D | 3 |
| 20 | Maxtor 6Y120P0 | 4 |
| 21 | Maxtor 6B200P0 | 4 |
| 22 | Samsung SP0812C | 4 |
| 23 | Seagate Barracuda ST3160023A | 4 |
| 24 | Seagate ST3120026A | 4 |
| 25 | DDR-400 256MB Kingston | 5 |
| 26 | DDR-400 256MB Hynix Original | 5 |
| 27 | DDR-400 256MB PQI | 5 |
| 28 | DDR-400 512MB Kingston | 5 |
| 29 | DDR-400 512MB PQI | 5 |
| 30 | DDR-400 512MB Hynix | 5 |
+------------+-------------------------------+------------+
При удалении категории из таблицы catalogs, в таблице products останутся товары которые не привязаны ни к одной из категорий, что может повлечь массу проблем для магазина.
mysql> DELETE FROM catalogs WHERE name = 'Процессоры';
mysql> SELECT * FROM catalogs;
+------------+-------------------------------------+
| id_catalog | name |
+------------+-------------------------------------+
| 2 | Материнские платы |
| 3 | Видеоадаптеры |
| 4 | Жёсткие диски |
| 5 | Оперативная память |
+------------+-------------------------------------+
mysql> SELECT * FROM products WHERE id_catalog = 1;
+------------+------------------------+------------+
| id_product | name | id_catalog |
+------------+------------------------+------------+
| 1 | Celeron 1.8 | 1 |
| 2 | Celeron 2.0GHz | 1 |
| 3 | Celeron 2.4GHz | 1 |
| 4 | Celeron D 320 2.4GHz | 1 |
| 5 | Celeron D 325 2.53GHz | 1 |
| 6 | Celeron D 315 2.26GHz | 1 |
| 7 | Intel Pentium 4 3.2GHz | 1 |
| 8 | Intel Pentium 4 3.0GHz | 1 |
| 9 | Intel Pentium 4 3.0GHz | 1 |
+------------+------------------------+------------+
Это явление называется нарушением ссылочной целостности На ссылочную целостность базы данных как правило оказывают четыре типа изменений:
- Добавление новой записи в таблице-потомке. Например добавление новой товарной позиции в таблицу products. Важно заметить что важную роль играет изменение именно таблицы-потомка, т.к изменение таблицы-предка (catalogs) не приведет к нарушению ссылочной целостности, т.к наличие пустой категории товаров допустимо
- Обновление внешнего ключа в таблице-потомке. Эта ситуация похожа на первую и может произойти при изменении у товара ссылки на несуществующий раздел каталога, например товар с id_catalog равным 50
- Удаление записи из таблицы-предка. Эта ситуация рассмотрена выше.
- Изменение записи в таблице-предке. Эта ситуация отличается от рассмотренной выше тем что категория каталога не удаляется а принимает новый id
Обработка изменений при помощи FOREIGN KEY
Для того что бы контролировать ссылочную целостность в базе данных необходимо что бы таблицы были связаны при помощи конструкции FOREIGN KEY, которая имеет вид: FOREIGN KEY [index_name] (index_col_name, …) REFERENCES tbl_name (index_col_name,…) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] FOREIGN KEY — используется при создании/изменении таблиц-потомков таблицах. В рамках данной статьи FOREIGN KEY, следует использовать в таблице products. Данная конструкция позволяет задать в таблице-потомке внешний ключ с именем index_name на столбцах таблицы которые перечисляется в круглых скобках. Можно использовать один или несколько столбцов. Ключевое слово REFERENCES задаёт таблицу-предка tbl_name на которую будет ссылаться внешний ключ. Поля таблицы-предка задаются в круглых скобках, один или несколько. Необязательные конструкции ON DELETE и ON UPDATE, определяют поведение MySQL при удалении/обновлении записей из таблицы-предка. Допустимые параметры для ключевых слов ON DELETE и ON UPDATE:
- RESTRICT — Если в таблице-потомке существуют записи ссылающиеся на первичный ключ таблицы-предка то при удалении или обновлении записей с этим первичным ключом в таблице предке, будет возвращена ошибка. Ошибка будет возвращаться до тех пор пока не останется ни одной ссылки в таблице потомке. В MySQL данный параметр означает то же самое что и NO ACTION
- CASCADE — При удалении/обновлении записей в таблице-предке, будут так же обновлены/удалены записи из таблицы-потомка с существующим первичным ключом
- SET NULL — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом будут обновлены на NULL
- NO ACTION — При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут. В MySQL данный параметр означает то же самое что и RESTRICT
- SET DEFAULT — Это действие зарезервировано но не обрабатывается в InnoDB
Добавление для таблицы products из примера статьи конструкции:
ALTER TABLE products ADD CONSTRAINT fk_catalog
FOREIGN KEY (id_catalog) REFERENCES catalogs (id_catalog)
ON DELETE CASCADE
ON UPDATE CASCADE
приведет к тому что изменения таблицы catalogs приведет к автоматическому изменению таблицы products. Проверку ограничения внешнего ключа можно отключить присвоив системной переменной FOREIGN_KEY_CHECKS значение 0
mysql> FOREIGN_KEY_CHECKS = 0;