SQL on_delete的删除选项

最新推荐文章于 2024-11-09 11:35:42 发布

转载最新推荐文章于 2024-11-09 11:35:42 发布 · 1.7k 阅读

RDBSM 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了当删除引用对象时可能出现的六种操作行为：级联删除、保护删除、设置为空、设置默认值、设置特定值及不采取任何行动。这些策略在数据库设计中至关重要，特别是对于确保数据完整性和避免意外数据丢失。

There are 6 possible actions to take when such event occurs:

CASCADE: When the referenced object is deleted, also delete the objects that have references to it (When you remove a blog post for instance, you might want to delete comments as well). SQL equivalent: CASCADE.
PROTECT: Forbid the deletion of the referenced object. To delete it you will have to delete all objects that reference it manually. SQL equivalent: RESTRICT.
SET_NULL: Set the reference to NULL (requires the field to be nullable). For instance, when you delete a User, you might want to keep the comments he posted on blog posts, but say it was posted by an anonymous (or deleted) user. SQL equivalent: SET NULL.
SET_DEFAULT: Set the default value. SQL equivalent: SET DEFAULT.
SET(...): Set a given value. This one is not part of the SQL standard and is entirely handled by Django.
DO_NOTHING: Probably a very bad idea since this would create integrity issues in your database (referencing an object that actually doesn't exist). SQL equivalent: NO ACTION.

Source: Django documentation

See also the documentation of PostGreSQL for instance.

In most cases, CASCADE is the expected behaviour, but for every ForeignKey, you should always ask yourself what is the expected behaviour in this situation. PROTECT and SET_NULL are often useful. Setting CASCADE where it should not, can potentially delete all your database in cascade, by simply deleting a single user.