MySQL. Урок 13. Внешние связи между таблицами. Операторы FOREGN KEY, REFERENCE, ON DELETE CASCADE | RESCTRICT.

-- Такие связи могут быть только при движке InnoDB.

-- Связываются поля с одинаковым типом данных.

-- Индексируем колонки в дочерней таблице и устанавливаем ссылку на колонку в родительской таблице.

-- Дочерняя таблица опирается только на PRIMARY KEY и UNNIQUE KEY.

DROP TABLE IF EXISTS table_parent;

-- Родительская таблица.

CREATE TABLE IF NOT EXISTS table_parent

(

id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Код товара',

code CHAR(4) NOT NULL DEFAULT 'AAAA' COMMENT 'Артикул товара',

name VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Название товара',

price FLOAT(8,2) NOT NULL DEFAULT 0.00 COMMENT 'Цена товара',

CONSTRAINT pkId PRIMARY KEY(id),

CONSTRAINT ixCode UNIQUE KEY(code)

) ENGINE=InnoDB COMMENT 'Таблица товаров';

DROP TABLE IF EXISTS table_child;

-- Дочерняя таблица.

CREATE TABLE IF NOT EXISTS table_child

(

id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Код записи',

code CHAR(4) NOT NULL DEFAULT 'AAAA' COMMENT 'Артикул товара',

qty FLOAT(8, 2) NOT NULL DEFAULT 0.00 COMMENT 'Приход или расход товара',

CONSTRAINT pkId PRIMARY KEY(id),

INDEX ixCode(code), -- Этот индекс должен быть обязательно.

CONSTRAINT fkTable_parentCode FOREIGN KEY(code)

REFERENCES table_parent(code)

ON DELETE RESTRICT

) ENGINE=InnoDB COMMENT 'Приход товаров на склад';

-- Вставляем данные в родительскую таблицу.

INSERT INTO table_parent(code,name, price)

VALUES ('XXXX', 'Товар 1', 56.99);

-- Вставляем данные в дочернюю таблицу.

INSERT INTO table_child

VALUES (NULL, 'XXXX', 20);

-- Посмотрим, как работают ограничения.

-- Удалим родительскую таблицу, которая связана с дочерней.

DROP TABLE table_parent; -- Ошибка.

-- Мы можем удалить дочернюю таблицу, а после этого родительскую.

-- Вставляем товар с индексом, которого нет, в дочернюю таблицу.

INSERT INTO table_child

VALUES (NULL, 'KKKK', 20); -- Ошибка

-- Удалим запись из родительской таблицы.

DELETE FROM table_parent WHERE code='XXXX'; -- Ошибка

-- Удалить можно только так:

DELETE FROM table_child WHERE code='XXXX';

DELETE FROM table_parent WHERE code='XXXX';

DROP TABLE IF EXISTS table_child;

-- Дочерняя таблица. Изменим RESTRICT на CASCADE

CREATE TABLE IF NOT EXISTS table_child

(

id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Код записи',

code CHAR(4) NOT NULL DEFAULT 'AAAA' COMMENT 'Артикул товара',

qty FLOAT(8, 2) NOT NULL DEFAULT 0.00 COMMENT 'Приход или расход товара',

CONSTRAINT pkId PRIMARY KEY(id),

INDEX ixCode(code), -- Этот индекс должен быть обязательно.

CONSTRAINT fkTable_parentCode FOREIGN KEY(code)

REFERENCES table_parent(code)

ON DELETE CASCADE

) ENGINE=InnoDB COMMENT 'Приход товаров на склад';

-- Вставляем данные в родительскую таблицу.

INSERT INTO table_parent(code,name, price)

VALUES ('XXXX', 'Товар 1', 56.99);

-- Вставляем данные в дочернюю таблицу.

INSERT INTO table_child

VALUES (NULL, 'XXXX', 20);

-- Посмотрим данные из родительской таблицы.

SELECT * FROM table_parent;

-- Удалим все из родительской таблицы.

DELETE FROM table_parent WHERE code='XXXX';

SELECT * FROM table_parent;

SELECT * FROM table_child; -- Пусто!

-- При удалении записи из родительской таблицы каскадно удалились данные из дочерней таблицы.