Does SQLite really not preserve default foreign key data integrity?

Question

Newer versions of SQLite support foreign key restrictions . Can define

CREATE TABLE MASTER (_ID INTEGER PRIMARY KEY, ...); CREATE TABLE SERVANT (_ID INTEGER PRIMARY KEY, MASTERID INTEGER, FOREIGN KEY(MASTERID) REFERENCES MASTER(_ID); 

According to the default documentation, "NO ACTION" is used for ON DELETE and ON UPDATE. But, contrary to other DBS, “NO ACTION” does not seem to mean that deletion or updating is not performed. Nothing seems to be done to maintain integrity, at least in my tests (*), and if I understand the documentation :

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

In this way,

 INSERT INTO MASTER (_ID) VALUES (1); INSERT INTO SERVANT (_ID, MASTERID) VALUES (1,1); DELETE FROM MASTER; 

gives me an empty MASTER table and a SERVANT table with a foreign key pointing to nowhere.

Can someone confirm this behavior and maybe explain why it is implemented that way? Or do I need to configure something to support working with a foreign key? I am new to SQLite development, so please provide me if this is a dumb question.

Edit: (*) my tests were wrong, see my answer below .

Answer 1

I will try to give an answer myself:

No, if it is configured correctly, SQLite maintains data integrity in this situation. "NO ACTION" is used by default, and this prohibits the removal or updating of the master key if there is still a link to the link from the link table (verified using 3.7.x). My mistake was that I did not know that PRAGMA foreign_keys = ON; must be configured for each new database connection.

Edit: I think the SQLite documentation is misleading here.

Answer 2

You're right. "NO ACTION" means nothing has been done to maintain the integrity of foreign key constraints. For more information on the options you can set, see the documentation .

In this scenario, you can install 4 more options. RESTRICT, SET NULL, SET DEFAULT and CASCADE. A brief description of what they do:

RESTRICT . A row in the MASTER table can only be deleted if it does not refer to any rows in the SERVANT table.

SET NULL Deleting a row in the MASTER table will cause all FKs in the SERVANT table to be set to NULL.

INSTALL DEFAULT . Like the NULL set, except that FK is set to the default value instead of NULL.

CASCADE . Deleting a row in the MASTER table will cause any rows in the SERVANT table to refer to the deleted MASTER row, which will also be deleted.

To change these settings, you will need to change your create statements to indicate the update and deletion actions.

 CREATE TABLE MASTER ( _ID INTEGER PRIMARY KEY, ... ); CREATE TABLE SERVANT ( _ID INTEGER PRIMARY KEY, MASTERID INTEGER, FOREIGN KEY(MASTERID) REFERENCES MASTER(_ID) ON UPDATE CASCADE ON DELETE SET NULL ); 

edit : Remember to make sure that your version of SQLite has been compiled with foreign key support and that you have enabled it by PRAGMA foreign_keys = ON;