PostgreSQL，MySQL for CASCADE, RESTRICT, NO ACTION, SET NULL, SET DEFAULT

最新推荐文章于 2024-07-12 21:13:30 发布

_Boboy

最新推荐文章于 2024-07-12 21:13:30 发布

阅读量397

点赞数

分类专栏： PostgreSQL 文章标签： postgresql mysql

本文链接：https://blog.csdn.net/weixin_43720095/article/details/112566176

版权

MySQL 同时被 2 个专栏收录

2 篇文章

订阅专栏

PostgreSQL

1 篇文章

订阅专栏

PostgreSQL

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.

链接：https://www.postgresql.org/docs/13/ddl-constraints.html#DDL-CONSTRAINTS-FK

MySQL

CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

Note

Cascaded foreign key actions do not activate triggers.
SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.
RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.
NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.
SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.