jonalv jonalv - 10 days ago 6
SQL Question

SQLite: Why is foreign key constraint not working here?

Based on https://www.sqlite.org/foreignkeys.html I thought I would get a foreign key constraint error on the last row of this SQL when executed in SQLite but it just swallows it all and moves on. Why is that?

CREATE TABLE Person (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100)
);

CREATE TABLE Child (
parent_id INTEGER,
child_id INTEGER,

FOREIGN KEY (parent_id) REFERENCES Person(id),
FOREIGN KEY (child_id) REFERENCES Person(id)
);

INSERT INTO Person(name)
VALUES ('John Doe');
INSERT INTO Person(name)
VALUES ('Clara Doe');

INSERT INTO Child(parent_id, child_id) VALUES (45, 50);

Answer

You need to first enable foreign key enforcement:

PRAGMA foreign_keys=on;