What exactly does SQLite's “NO ACTION” foreign key constraint do, and how does it differ from “RESTRICT”?

Question

The documentation says:

Setting "NO ACTION" means only this: when the parent key is changed or deleted from the database, no special actions are taken.

My first interpretation of this sentence was "if the parent key is changed or deleted, then this modification is completed and no other actions are taken", thus, without preserving the integrity of the database, which leads to some confusion . But my testing showed that if I try to delete the parent key (if the child key still exists), I get an exception ("SQLiteConstraintException: error code 19: constraint failed" - I'm testing in android 4.0.3 / SQLite 3.7.x) therefore, “NO ACTION” seems to behave as expected.

Can someone explain and perhaps give an example of what exactly “NO ACTION” does and how it differs from “RESTRICT”.

Answer 1

The opening paragraph reads:

If an action is not explicitly specified, the default value is "NO ACTION".

This is a normal action.

Further

The difference between the effect of the RESTRICT action and the normal forced restriction of the foreign key is that the processing of the RESTRICT action occurs as soon as the field is updated, and not at the end of the current statement, as it would with a direct restriction, or at the end of the current transaction, as it were would be with a deferred constraint.

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