Внешние ключи

Обзор

Внешний ключ — это ограничение, которое можно использовать для обеспечения целостности данных. Он состоит из столбца (или набора столбцов) в таблице, называемой дочерней таблицей, которая ссылается на столбец (или набор столбцов) в таблице, называемой родительской таблицей. Если используются внешние ключи, MariaDB выполняет некоторые проверки, чтобы обеспечить соблюдение правил целостности. Для более подробного объяснения см. Реляционные базы данных: Внешние ключи.

Внешние ключи могут использоваться только с хранилищами, которые поддерживают их. По умолчанию InnoDB и устаревшее PBXT поддерживают внешние ключи.

Размеченные таблицы не могут содержать внешние ключи и не могут быть ссылками для внешних ключей.

Синтаксис

CREATE TABLE b(for_key INT REFERENCES a(not_key));

Внешние ключи создаются с помощью CREATE TABLE или ALTER TABLE. Определение должно соответствовать этому синтаксису:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Предложение symbol, если указано, используется в сообщениях об ошибках и должно быть уникальным в базе данных.

Столбцы в дочерней таблице должны быть индексом BTREE (не HASH, RTREE или FULLTEXT — см. SHOW INDEX) или левой частью индекса BTREE. Префиксы индексов не поддерживаются (поэтому столбцы TEXT и BLOB не могут использоваться в качестве внешних ключей). Если MariaDB автоматически создаёт индекс для внешнего ключа (потому что он не существует и не создан явно), его имя будет index_name.

Ссылка на столбцы в родительской таблице должна быть индексом или префиксом индекса.

Столбцы внешнего ключа и столбцы ссылки должны иметь одинаковый тип или похожие типы. Для целочисленных типов размер и знак также должны быть одинаковыми.

Столбцы внешнего ключа и столбцы ссылки могут быть столбцами PERSISTENT. Однако предложения ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL в этом случае не допускаются.

Родительская и дочерняя таблицы должны использовать один и тот же движок хранения и не должны быть TEMPORARY или размеченными таблицами. Они могут быть одной и той же таблицей.

Ограничения

Если внешний ключ существует, каждая строка в дочерней таблице должна соответствовать строке в родительской таблице. Несколько строк дочерней таблицы могут соответствовать одной строке родительской таблицы. Строка дочерней таблицы соответствует строке родительской таблицы, если все её значения внешнего ключа идентичны значениям родительской строки в родительской таблице. Однако, если хотя бы одно из значений внешнего ключа NULL, у строки нет родителей, но она все равно разрешена.

MariaDB выполняет определенные проверки, чтобы гарантировать соблюдение целостности данных:

• Попытка вставить несоответствующие строки (или обновить соответствующие строки таким образом, что они станут несоответствующими) в дочернюю таблицу приводит к ошибке 1452 (SQLSTATE '23000').
• При удалении строки в родительской таблице и наличии по крайней мере одной строки дочерней таблицы MariaDB выполняет действие, которое зависит от предложения ON DELETE внешнего ключа.
• При изменении значения в столбце, на который ссылается внешний ключ, и наличии по крайней мере одной строки дочерней таблицы MariaDB выполняет действие, которое зависит от предложения ON UPDATE внешнего ключа.
• Попытка удалить таблицу, на которую ссылается внешний ключ, приводит к ошибке 1217 (SQLSTATE '23000').
• TRUNCATE TABLE для таблицы, содержащей один или несколько внешних ключей, выполняется как DELETE без WHERE, чтобы внешние ключи применялись к каждой строке.

Допустимые действия для ON DELETE и ON UPDATE:

• RESTRICT: Изменение родительской таблицы запрещено. Оператор завершается ошибкой 1451 (SQLSTATE '2300'). Это поведение по умолчанию для ON DELETE и ON UPDATE.
• NO ACTION: Синоним RESTRICT.
• CASCADE: Изменение разрешено и распространяется на дочернюю таблицу. Например, при удалении строки родительской таблицы также удаляется строка дочерней таблицы; при изменении ID строки родительской таблицы ID строки дочерней таблицы также изменится.
• SET NULL: Изменение разрешено, и столбцы внешнего ключа дочерней строки устанавливаются в NULL.
• SET DEFAULT: Работал только с PBXT. Похож на SET NULL, но столбцы внешнего ключа устанавливались в их значения по умолчанию. Если значений по умолчанию не существует, генерируется ошибка.

Операции удаления или обновления, вызываемые внешними ключами, не активируют триггеры и не учитываются в переменных состояния сервера Com_delete и Com_update.

Ограничения внешних ключей могут быть отключены путём установки переменной сервера foreign_key_checks в значение 0. Это ускоряет вставку больших объёмов данных.

Метаданные

Таблица Information Schema REFERENTIAL_CONSTRAINTS содержит информацию о внешних ключах. Отдельные столбцы перечислены в таблице KEY_COLUMN_USAGE.

Таблицы Information Schema, специфичные для InnoDB, также содержат информацию о внешних ключах InnoDB. Информация о внешних ключах хранится в INNODB_SYS_FOREIGN. Данные об отдельных столбцах хранятся в INNODB_SYS_FOREIGN_COLS.

Иногда самым понятным способом получить информацию о внешних ключах таблицы является оператор SHOW CREATE TABLE.

Ограничения

Внешние ключи имеют следующие ограничения в MariaDB:

• В настоящее время внешние ключи поддерживаются только InnoDB.
• Не могут использоваться с представлениями.
• Действие SET DEFAULT не поддерживается.
• Действия внешних ключей не активируют триггеры.
• Если ON UPDATE CASCADE выполняет рекурсивное обновление той же таблицы, которую она уже обновила во время каскадного обновления, он действует как RESTRICT.

Примеры

Рассмотрим пример. Мы создадим таблицу author и таблицу book. Обе таблицы имеют первичный ключ, называемый id. Таблица book также имеет внешний ключ, состоящий из поля, называемого author_id, который ссылается на первичный ключ author. Ограничение внешнего ключа — необязательно, но мы укажем его, поскольку хотим, чтобы оно отображалось в сообщениях об ошибках: fk_book_author.

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

Теперь, если мы попытаемся вставить книгу с несуществующим автором, мы получим ошибку:

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Ошибка очень информативна.

Теперь давайте попробуем правильно вставить двух авторов и их книги:

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

Всё получилось!

Теперь давайте удалим второго автора. При создании внешнего ключа мы указали ON DELETE CASCADE. Это должно распространить удаление и удалить книги удалённого автора:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

Мы также указали ON UPDATE RESTRICT. Это должно помешать нам изменить id автора (столбец, на который ссылается внешний ключ), если существует строка дочерней таблицы:

UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

REFERENCES

До MariaDB 10.4

CREATE TABLE a(a_key INT primary key, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));

SHOW CREATE TABLE b;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `for_key` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+

INSERT INTO a VALUES (1,10);
Query OK, 1 row affected (0.005 sec)

INSERT INTO b VALUES (10);
Query OK, 1 row affected (0.004 sec)

INSERT INTO b VALUES (1);
Query OK, 1 row affected (0.004 sec)

SELECT * FROM b;
+---------+
| for_key |
+---------+
|      10 |
|       1 |
+---------+

Начиная с MariaDB 10.5

CREATE TABLE a(a_key INT primary key, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));
ERROR 1005 (HY000): Can't create table `test`.`b` 
  (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE c(for_key INT REFERENCES a(a_key));

SHOW CREATE TABLE c;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `for_key` int(11) DEFAULT NULL,
  KEY `for_key` (`for_key`),
  CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+

INSERT INTO a VALUES (1,10);
Query OK, 1 row affected (0.004 sec)

INSERT INTO c VALUES (10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
  (`test`.`c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`))

INSERT INTO c VALUES (1);
Query OK, 1 row affected (0.004 sec)

SELECT * FROM c;
+---------+
| for_key |
+---------+
|       1 |
+---------+

См. также

• MariaDB: InnoDB foreign key constraint errors, запись в блоге MariaDB