Foreign key constraint not working in sqlite3

Question

• python version: 3.8.10
• sqlite3.version: 2.6.0
• sqlite3.sqlite_version: 3.31.1

I have two tables: blocklist and blocklist_reasons. See below:

blocklist_reasons
id | reason
--------------------------
1  | Source not supported

blocklist
id | link            | reason
-----------------------------
1  | https://mega.nz | 1

blocklist.reason references blocklist_reasons.id, so a foreign key constraint is set up.

However, I can insert any number I want and it will still accept it, which is strange:

sqlite> INSERT INTO blocklist(link, reason) VALUES ('https://example.com', 2);
sqlite> -- No problems eventhough there is no row in blocklist_reasons with ID 2???
sqlite> SELECT * FROM blocklist;
1|https://mega.nz|1
2|https://example.com|2

I was able to reproduce this problem with the following code:

-- Create reasons table and add one reason with ID 1
CREATE TABLE blocklist_reasons(
    id INTEGER PRIMARY KEY,
    reason TEXT NOT NULL UNIQUE
);
INSERT INTO blocklist_reasons(reason) VALUES ('Source not supported');
SELECT * FROM blocklist_reasons;
1|Source not supported

-- Create blocklist table and insert row with reason ID set as ID 2 without errors
CREATE TABLE blocklist(
    id INTEGER PRIMARY KEY,
    link TEXT NOT NULL,
    reason INTEGER NOT NULL,
    FOREIGN KEY (reason) REFERENCES blocklist_reasons(id)
);
INSERT INTO blocklist(link, reason) VALUES ('https://mega.nz', 2);
SELECT * FROM blocklist;
1|https://mega.nz|2

You can see that I inserted a row with the reason column set to 2 and it worked fine, eventhough there is no row in the blocklist_reasons table with an ID of 2. Why isn't the foreign key constraint raising an error?

Answer 1

Turns out I have to ENABLE foreign key checking. In my opinion stupid but alright. Everytime you start a database connection (not a cursor connection but a database connection), run the following command:

PRAGMA foreign_keys = ON;

This enables the constraint.