Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What exactly does SQLite's "NO ACTION" foreign key constraint do and how is it different from "RESTRICT"?

Tags:

android

sqlite

The documentation says:

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".

like image 615
yonojoy Avatar asked Jul 11 '13 19:07

yonojoy


1 Answers

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.

like image 54
CL. Avatar answered Oct 21 '22 11:10

CL.