Главная > Блог > Web-разработка > PHP и MySQL >

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

27.04.2019

Теги: MySQL • Web-разработка • БазаДанных • Ключ

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

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

[CONSTRAINT] REFERENCES главная_таблица (столбец_главной_таблицы)
[ON DELETE действие]
[ON UPDATE действие]

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE, которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

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

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

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE `customers` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `age` int, 
    `name` varchar(50) NOT NULL,
    `surname` varchar(50) NOT NULL,
    `phone` varchar(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `customer_id` int REFERENCES `customers` (`id`),
    `created` date
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

В данном случае определены таблицы customers и orders. Таблица customers является главной и представляет клиента. Таблица orders является зависимой и представляет заказ, сделанный клиентом. Таблица orders через столбец customer_id связана с таблицей customers и ее столбцом id. То есть столбец customer_id является внешним ключом, который указывает на столбец id из таблицы customers.

Мы также могли бы определить внешний ключ на уровне таблицы:

CREATE TABLE `orders` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `customer_id` int,
    `created` date,
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

CREATE TABLE `orders` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `customer_id` int,
    `created` date,
    CONSTRAINT `orders_customers_fk`
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ON DELETE и ON UPDATE

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

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

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE:

CREATE TABLE `orders` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `customer_id` int,
    `created` date,
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Подобным образом работает и выражение ON UPDATE CASCADE. При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.

Установка NULL

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

Поиск: MySQL • Web-разработка • База данных • Ключ