Does SQLite really not preserve data integrity of foreign key constraints by default?

I will try to give an answer myself:

No, if configured right, SQLite preserves data integrity in this situation. "NO ACTION" is used by default and this prohibits deletion or update of a master key if there is still a refering key from an referencing table (tested with 3.7.x). My fault was that I was not aware that PRAGMA foreign_keys = ON; must be configured for every new connection to the database.

Edit: I think the SQLite documentation is misleading here.

You are correct. "NO ACTION" means that nothing is done to preserve the integrity of the foreign key constraints. See the documentation for details on options you can set.

There are 4 other options you can set in this scenario. 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 is not referenced by any rows in the SERVANT table.

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

SET DEFAULT - Similar to set NULL except that the 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 that reference the deleted MASTER row to also be deleted.

To change these options, you will have to modify your create statements to specify the on update and on delete 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:Don't forget to ensure that your version of SQLite was compiled with foreign key support and that you've enabled it by specifying PRAGMA foreign_keys = ON;