Когда использовать "ON UPDATE CASCADE"

Question

Я регулярно использую ON DELETE CASCADE, но никогда не использовал ON UPDATE CASCADE, так как не уверен, в каких ситуациях это может быть полезно.

Для обсуждения давайте посмотрим на некоторый код.

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

При использовании ON DELETE CASCADE, если родительская запись с id будет удалена, запись в таблице child с parent_id = parent.id будет автоматически удалена. Это не вызывает проблем.

Значит ли это, что ON UPDATE CASCADE будет делать то же самое, когда id родителя обновляется?
Если (1) верно, это означает, что нет необходимости использовать ON UPDATE CASCADE, если parent.id не подлежит обновлению (или никогда не будет обновляться), например, когда он является AUTO_INCREMENT или всегда устанавливается на TIMESTAMP. Так ли это?
Если (2) неверно, в каких других ситуациях следует использовать ON UPDATE CASCADE?
Что произойдет, если я (по какой-то причине) обновлю child.parent_id на несуществующее значение? Будет ли оно тогда автоматически удалено?

Я знаю, что некоторые из этих вопросов можно протестировать программно, чтобы понять, но мне также интересно, зависят ли они от конкретного поставщика базы данных или нет.

Пожалуйста, проясните ситуацию.

Answer 1

Да, действительно, если ваш первичный ключ представляет собой просто значение, автоматически увеличиваемое (identity), то вам не потребуется использовать ON UPDATE CASCADE.

Однако, представьте, что ваш первичный ключ — это 10-значный штрих-код UPC, и из-за расширения бизнеса вам нужно изменить его на 13-значный штрих-код. В таком случае использование ON UPDATE CASCADE позволит вам изменить значение первичного ключа, и все таблицы, которые ссылаются на это значение через внешние ключи, будут обновлены соответствующим образом.

Что касается пункта #4, если вы измените идентификатор дочерней таблицы на значение, которого не существует в родительской таблице (и у вас настроена ссылочная целостность), вы получите ошибку нарушения внешнего ключа.

Answer 2

Да, это означает, что если вы выполните UPDATE parent SET id = 20 WHERE id = 10, то все дочерние записи с parent_id, равным 10, также будут обновлены на 20.
Если вы не обновляете поле, на которое ссылается внешний ключ, то эта настройка не нужна.
Не могу придумать другого применения.
Это невозможно, так как ограничение внешнего ключа не позволит сделать такое обновление.

Answer 3

Я думаю, вы в целом правильно подметили основные моменты!

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

Zed также правильно заметил, что если вы используете естественный ключ (например, обычное поле из вашей таблицы базы данных) в качестве первичного ключа, то могут возникнуть ситуации, когда вам потребуется обновить ваш первичный ключ. Недавний пример — это ISBN (международный стандартный номер книги), который изменился с 10 на 13 цифр и символов не так давно.

Однако это не касается случаев, когда вы выбираете суррогатные (например, искусственно сгенерированные) ключи в качестве первичного ключа (это будет мой предпочтительный выбор, за исключением самых редких случаев).

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

Answer 4

Несколько дней назад у меня возникла проблема с триггерами, и я понял, что ON UPDATE CASCADE может быть полезен. Вот пример на PostgreSQL:

CREATE TABLE club
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE band
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE concert
(
    key SERIAL PRIMARY KEY,
    club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
    band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
    concert_date DATE
);

В моей ситуации мне нужно было определить некоторые дополнительные операции (триггер) для обновления таблицы concert. Эти операции должны были модифицировать значения club_name и band_name. Я не мог сделать это из-за ограничений внешних ключей. Я не мог сначала изменить данные в таблице concert, а затем корректировать таблицы club и band. И, наоборот, тоже не получалось. Использование ON UPDATE CASCADE стало ключом к решению проблемы, так как оно позволило автоматически обновлять связанные данные в таблицах при изменении значений в club и band.

Answer 5

Это отличный вопрос, я сам задавался им вчера. Я подумал о данной проблеме и специально искал что-то вроде "ON UPDATE CASCADE", и, к счастью, дизайнеры SQL тоже об этом позаботились. Я согласен с Ted.strauss и также прокомментировал случай Noran'a.

Когда я использовал эту возможность? Как заметил Тед, когда работаешь с несколькими базами данных одновременно, и при изменении одной из них, в одной таблице, это изменение должно каким-то образом отразиться на так называемой "сателлитной базе данных". В этом случае ID оригинальной записи не всегда можно сохранить, и по какой-либо причине вам нужно создать новый, если вы не можете обновить данные в старом (например, из-за ограничений по правам доступа или если вы ищете быстродействие в ситуации, когда данные временные и не заслуживают строгого соблюдения всех правил нормализации, ведь они будут использоваться лишь короткое время).

Таким образом, я согласен с двумя пунктами:

(A.) Да, в многих случаях более продуманный дизайн может избежать этого; НО

(B.) В случаях миграций, репликации баз данных или решения неотложных задач это ОТЛИЧНЫЙ ИНСТРУМЕНТ, которого, к счастью, я нашел, когда начал искать, существует ли он.

Когда использовать "ON UPDATE CASCADE"

5 ответ(ов)

Руководство по Markdown

Форматирование текста

Код

Списки

Поддерживаемые языки программирования

Популярные

Другие .NET

Базы данных