Non-existent foreign key doesn't cause error in SQLite

Question

The following code should IMO produce an error because the user_id=1 doesn't exist. Why does it work?

CREATE TABLE users (
user_id int PRIMARY KEY,
email text UNIQUE
);

CREATE TABLE claimed (
account_id int PRIMARY KEY,
user_id int,
domain text,
FOREIGN KEY (user_id) REFERENCES users (user_id)
);

INSERT INTO claimed
(user_id, domain)
VALUES (1, "abcd");

Answer 1

From the relevant documentation:

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 (2009-10-14) - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced.

and:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

and:

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Apparently you should use PRAGMA foreign_keys = ON at the top of your connection, and potentially rebuild with the appropriate options (though if you installed from package then I would personally assume that this has been done).

_{Source: Google sqlite foreign key, first result}