Search code examples
mysqlsqlpostgresqlcascading-deletes

What are the corresponding configs in MySQL for deletion behaviors in PostgreSQL for CASCADE, RESTRICT, NO ACTION, SET NULL, SET DEFAULT

I have experience with Postgres and their deletion behavior for foreign key references (quoting from the PostgreSQL docs):

  • NO ACTION (default): If any referencing rows still exist when the constraint is checked, an error is raised
  • RESTRICT: Prevents deletion of a referenced row. The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.
  • CASCADE: When a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
  • SET NULL: Causes the referencing columns to be set to NULL when the referenced row is deleted.
  • SET DEFAULT: Causes the referencing columns to be set to their default values when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the operation will fail.

Are there corresponding configs for all of these behaviors in MySQL as well? I was looking through the docs but didn't find anything helpful...

Solution

  • You can find the mysql docs here:

    https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html 

    reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT