Каскадные операции БД

Теги: mysql innodb

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

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

Допустим у нас есть таблица с годами:

CREATE TABLE `year` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `year` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

таблица с месяцами:

CREATE TABLE `month` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `month` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

и таблица с днями:

CREATE TABLE `day` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `day` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Добавим связь (с действием CASCADE) между таблицами дней и месяцев:

-- добавляем поле для хранения id родительской записи
ALTER TABLE `day` ADD COLUMN `monthId` INT NOT NULL;
-- добавляем внешний ключ
ALTER TABLE `day` ADD CONSTRAINT `FK_day_month` FOREIGN KEY (`monthId`) REFERENCES `month` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;

И добавим такую же связь между таблицами месяцев и годов:

-- добавляем поле для хранения id родительской записи
ALTER TABLE `month` ADD COLUMN `yearId` INT(11) NOT NULL;
-- добавляем внешний ключ
ALTER TABLE `month` ADD CONSTRAINT `FK_month_year` FOREIGN KEY (`yearId`) REFERENCES `year` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;

Теперь:

  1. При удалении строки из таблицы годов автоматически удалятся связанные строки из таблицы месяцев и связанные с ними строки из таблицы дней.

  2. При обновлении id в строке из таблицы годов или месяцев, автоматически обновятся поля связи в таблицах месяцев или дней соответственно.

Проверим это.

Заполним таблицы тестовыми данными:

DELETE from `year` WHERE 1;
ALTER TABLE `year` AUTO_INCREMENT=1;
INSERT INTO `year` (`year`) VALUES (2018), (2019);
DELETE from `month` WHERE 1;
ALTER TABLE `month` AUTO_INCREMENT=1;
INSERT INTO `month` (`month`, `yearId`) VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1), (10, 1), (11, 1), (12, 1), (1, 2), (2, 2), (3, 2), (4, 2), (5, 2), (6, 2), (7, 2), (8, 2), (9, 2), (10, 2), (11, 2), (12, 2);
-- для краткости заполним только 3 дня на каждый месяц
DELETE from `day` WHERE 1;
ALTER TABLE `day` AUTO_INCREMENT=1;
INSERT INTO `day` (`day`, `monthId`) VALUES (1, 1), (2, 1), (3, 1), (1, 1), (2, 1), (3, 2), (1, 1), (2, 1), (3, 3), (1, 1), (2, 1), (3, 4), (1, 1), (2, 1), (3, 5), (1, 1), (2, 1), (3, 6), (1, 1), (2, 1), (3, 7), (1, 1), (2, 1), (3, 8), (1, 1), (2, 1), (3, 9), (1, 1), (2, 1), (3, 10), (1, 1), (2, 1), (3, 11), (1, 1), (2, 1), (3, 12), (1, 1), (2, 1), (3, 13), (1, 1), (2, 1), (3, 14), (1, 1), (2, 1), (3, 15), (1, 1), (2, 1), (3, 16), (1, 1), (2, 1), (3, 17), (1, 1), (2, 1), (3, 18), (1, 1), (2, 1), (3, 19), (1, 1), (2, 1), (3, 20), (1, 1), (2, 1), (3, 21), (1, 1), (2, 1), (3, 22), (1, 1), (2, 1), (3, 23), (1, 1), (2, 1), (3, 24);

Удаляем год:

DELETE FROM `year` WHERE `id` = 1;

И видим, что пропали так же и дочерние месяцы и их дочерние дни.

Попробуем обновить id оставшегося года:

UPDATE `year` SET `id` = 3 WHERE `id` = 2;

Id так же обновился в полях yearId в таблице месяцев.

Кроме CASCADE:

... ON UPDATE CASCADE ON DELETE CASCADE

можно использовать следующие действия при обновлении и удалении записи родительской таблицы:

NO ACTION - ничего не делать

RESTRICT - запрещает выполнять действие (удалять строку и обновлять поле с ключем) над записью родительской таблицы пока существует хотя бы одна запись в дочерней таблице, ссылающаяся на ключ в родительской таблице

SET NULL - выставляет значение дочернего ключа как NULL (для этого поле должно иметь такую возможность)