Home / Questions / 105890
Accepted
Angel Zambrano
Asked: 2020-09-29 14:26:14 +0800 CST 2020-09-29 14:26:14 +0800 CST

Что означает CASCADE, SET NULL, RESTRICT, NO ACTION в MySQL?

  • 772

Я работал с реляционными таблицами, но оказалось, что есть 4 типа "действий" при удалении или обновлении дочерней строки и я не знаю, что это может означать, хотя я поставил CASCADE на все из них, не особо зная их функцию.

Они помогают мне?

введите описание изображения здесь

mysql

3 Answers

  1. Best Answer

    Они называются ограничениями ссылочной целостности .

    Отношения между различными таблицами базы данных MySQL, которые используют механизм хранения InnoDB, могут быть указаны в виде ограничений внешнего ключа («Foreign Key Constraints»), так что сама база данных предотвращает выполнение операций, которые могут вызвать несоответствия.

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

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

    Изображения рассматривают две таблицы personasи ciudadesсвязаны столбцом ciudad_id:

    ДИЗАЙН СТОЛА

    Изображение А. Чедано для stackoverflow.es

    RESTRICT

    • RESTRICT: это поведение по умолчанию, которое предотвращает модификации, нарушающие ссылочную целостность.

      На изображении мы видим результат этого запроса:

      DELETE FROM ciudades WHERE ciudad_id=4;

      Мы видим, что запись может быть удалена, так как в таблице нет связанной записи personas.

    ОГРАНИЧИВАТЬ

    Изображение А. Чедано для stackoverflow.es

    Вместо этого запроса:

        DELETE FROM ciudades WHERE ciudad_id=1;

    Это выдаст сообщение об ошибке:

    Невозможно удалить или обновить родительскую строку: ограничение внешнего ключа не работает ( db. personas, CONSTRAINT personas_ibfk_1FOREIGN KEY ( ciudad_id) REFERENCES ciudades( ciudad_id))

    Потому что DELETEон нарушает ограничение. Если бы строка 1of ciudadesбыла удалена, строки 1и 4of personasбыли бы потеряны , то есть не имели бы связи в таблице ciudades.

    CASCADE

    • CASCADE: Удаляет записи зависимой таблицы при удалении записи родительской таблицы (в инструкции DELETE) или обновляет значение дочернего ключа при обновлении значения ключа, на который указывает ссылка (в инструкции UPDATE).

      На изображении мы видим результат этого запроса:

      DELETE FROM ciudades WHERE ciudad_id=1;

      CASCADE Здесь все записи равные будут удалены в cascade personas, и как видно будет удалено в городе с id .ciudad_id1ciudades1

    НА УДАЛЕНИЕ КАСКАД

    Изображение А. Чедано для stackoverflow.es

    SET NULL

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

      То, что мы видим на изображении, является результатом этого запроса:

      DELETE FROM ciudades WHERE ciudad_id=1;

      Здесь в столбце ciudad_idтаблицы personasбудут установлены значения NULL, во всех строках, ciudad_idкоторые равны 1. И как видно, он будет удален в ciudadesгороде с id 1.

    НА УДАЛИТЬ УСТАНОВИТЬ НУЛЕВОЕ

    Изображение А. Чедано для stackoverflow.es

    Для того, что происходит в этом случае, важно отметить, что это CREATE TABLE:

    CREATE TABLE IF NOT EXISTS personas 
(
    persona_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    persona_nom VARCHAR(70),
    ciudad_id INT NOT NULL,
    FOREIGN KEY fk_ciudad(ciudad_id) REFERENCES ciudades(ciudad_id)
    ON DELETE SET NULL

)ENGINE=INNODB;

    Он выдаст ошибку:

    Невозможно добавить ограничение внешнего ключа

    Думаю не сложно догадаться почему :)

    NO ACTION

    • NO ACTION: В MySQL это работает так же, как RESTRICT. См. объяснение ниже.

    тестовый код

    Я оставляю здесь демонстрацию, чтобы мы могли проверить на реальных данных, как работает каждое ограничение. Нам нужно будет только изменить конфигурацию ограничения CREATE TABLE, поместив различные возможности.

    Пример основан на DELETE, но при желании мы можем написать и протестировать запросы UPDATE.

    Это ссылка.

    VER DEMO

    Подробнее об ограничениях

    Все, что следует далее, почти полностью взято из документации MySQL.

    Ограничения, как первичные, так и внешние ключи, обычно указываются при создании таблицы ( CREATE TABLE). Те, которые относятся к ссылочной целостности, должны быть указаны явно, так как MySQL не выводит их на основе указанных первично-внешних ключей. Если они не созданы во время CREATE TABLE, их можно изменить позже с помощью ALTER TABLE. В документации объясняется, как это сделать.

    Существует два типа ограничений: ON DELETEи ON UPDATE. А внутри них по очереди несколько возможностей (одинаково для обоих).

    Давайте объясним это с помощью документации :

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

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

    • CASCADE: удалить или обновить строку родительской таблицы и автоматически удалить или обновить соответствующие строки в дочерней таблице. Пока ON DELETE CASCADEони ON UPDATE CASCADEсовместимы. Между двумя таблицами не определены несколько предложений ON UPDATE CASCADE, которые действуют на один и тот же столбец как в родительской, так и в дочерней таблицах .

    Примечание Каскадные действия внешнего ключа не активируют триггеры.

    • SET NULL: удалить или обновить строку родительской таблицы и установить для столбцов внешнего ключа в дочерней таблице значение NULL. ON DELETE SET NULLПредложения и поддерживаются ON UPDATE SET NULL.

    Если вы укажете действие SET NULL, убедитесь, что вы не объявили столбцы дочерней таблицы как NOT NULL.

    • RESTRICT: отклоняет операцию удаления или обновления для родительской таблицы. Указание RESTRICT(или ) равносильно пропуску NO ACTIONпредложения ON DELETEили ON UPDATE.

    • NO ACTION: стандартное ключевое слово SQL. В MySQL эквивалентно RESTRICT. Сервер MySQL отклоняет операцию удаления или обновления для родительской таблицы, если в таблице, на которую ссылаются, есть связанное значение внешнего ключа. Некоторые системы баз данных имеют отложенные проверки, и NO ACTIONэто отложенная проверка. В MySQL ограничения внешнего ключа проверяются немедленно, так что NO ACTIONэто то же самое, что и RESTRICT.

    • SET DEFAULT: это действие распознается синтаксическим анализатором MySQL, но и InnoDB, и NDB отклоняют определения таблиц, которые содержат предложения ON DELETE SET DEFAULTили ON UPDATE SET DEFAULT.

    Для того ON DELETEили ON UPDATEиного не указано, действие по умолчанию всегда RESTRICT.

    MySQL поддерживает ссылки внешнего ключа между одним столбцом и другим в таблице. (Столбец не может иметь ссылку на себя по внешнему ключу.) В этих случаях «записи дочерней таблицы» фактически относятся к зависимым записям в той же таблице.

    Ограничение внешнего ключа для сохраненного сгенерированного столбца не может использовать ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULLили .ON DELETE SET DEFAULTON UPDATE SET DEFAULT

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

    Ограничения InnoDB, связанные с внешними ключами и сгенерированными столбцами, см. в разделе 14.8.1.6, «Ограничения InnoDB и FOREIGN KEY» .

    • 68

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

    • CASCADE: когда вы делаете UPDATE/ DELETEв родительской таблице, он автоматически обновляет/удаляет связанные строки в дочерней таблице.
    • SET NULL: при выполнении UPDATE/ DELETEв родительской таблице внешние ключи связанных строк в дочерней таблице будут установлены на NULL. Важно убедиться, что эти поля не будут ограничены NOT NULLили вы можете получить ошибки.
    • RESTRICT: если вы попытаетесь выполнить действие UPDATE/ DELETEнад родительской таблицей, оно будет автоматически отклонено.
    • NO ACTION: это ключевое слово SQL, в MySQL оно эквивалентно RESTRICT.

    Существует пятое значение SET DEFAULT, которое, хотя и принимается процессором MySQL, может вызвать проблемы с InnoDB и NDB, которые будут отклонять таблицы, определенные с этим значением.

    • 13

  3. В MySQL только механизм InnoDB в настоящее время поддерживает ограничения внешнего ключа. Синтаксис ограничений внешнего ключа в InnoDB следующий:

    
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_NAME] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

    КАСКАД:

    При обновлении/удалении записей в родительской таблице одновременно обновляйте/удаляйте соответствующие записи в дочерней таблице.

    УСТАНОВИТЬ НУЛЬ：

    При обновлении/удалении записей в родительской таблице установите для столбца соответствующей записи в дочерней таблице значение null [обратите внимание, что соответствующий столбец внешнего ключа в дочерней таблице не может быть установлен в NOT NULL]

    БЕЗДЕЙСТВИЕ:

    Отказаться от обновления или удаления или удаления родительской таблицы

    ОГРАНИЧИВАТЬ :

    Отказаться от обновления или удаления родительской таблицы

    Примечание. Указание RESTRICT или NOACTION имеет тот же эффект, что и игнорирование ON DELETE или ON UPDATE.

    • 0