restrict 外键约束,究竟是什么的SQLite的[否动作＆QUOT;外键约束做的，它是如何从＆QUOT不同; RESTRICT＆QUOT;？...

最新推荐文章于 2020-12-21 05:52:21 发布

转载最新推荐文章于 2020-12-21 05:52:21 发布 · 111 阅读

文章标签：

#restrict 外键约束

本文探讨了在数据库中配置NOACTION的作用及其与RESTRICT的区别。当父键被修改或删除时，NOACTION确保不会触发额外的操作，而RESTRICT则在相关子记录存在时阻止此类操作。文章通过测试示例说明两者的行为差异。

Configuring "NO ACTION" means just that: when a parent key is modified

or deleted from the database, no special action is taken.

My first interpretation of this sentence was "if parent key is modified or deleted, then this modification is done and no other action is taken" thus not preserving database integrity, which lead to some confusion.

But my testing showed, that if I try to delete a parent key (if a child key still exists), I do get an exception ("SQLiteConstraintException: error code 19: constraint failed" - I am testing under android 4.0.3 / SQLite 3.7.x), so "NO ACTION" seems to behave as expected.

Could someone please explain and perhaps give an example, what exactly "NO ACTION" does and how it is different from "RESTRICT".

解决方案

The introducing paragraph says:

If an action is not explicitly specified, it defaults to "NO ACTION".

This is the normal action.

Furthermore:

The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint.

If you're testing with a single-statement transaction that changes just one record, you will not see any difference between NO ACTION and RESTRICT.