How do Foreign Key constraints work in SQLite and why must they be enabled explicitly?

Explanation & Concept

For backwards compatibility with legacy SQLite 2 databases, SQLite has foreign key constraint enforcement **disabled by default**. To enforce foreign key checks and cascade rules, applications must execute `PRAGMA foreign_keys = ON;` upon opening every database connection.

Code Example

sql

-- Must run on EVERY new connection!
PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

How It Works & Key Takeaways

Without executing `PRAGMA foreign_keys = ON;`, SQLite silently permits inserting non-existent `author_id` values and ignores `ON DELETE CASCADE` rules.

Mock Test · 1 Questions · Optional

Ready for SQLite vs Other DBs?

Take a quick test to evaluate yourself, or move straight to the next module.

Intermediate1 questions

Skip

Foreign KeysBeginner

1Question 1 of 1

sql

How do Foreign Key constraints work in SQLite and why must they be enabled explicitly?

Explanation & Concept

Code Example

sql

-- Must run on EVERY new connection!
PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

How It Works & Key Takeaways

Without executing `PRAGMA foreign_keys = ON;`, SQLite silently permits inserting non-existent `author_id` values and ignores `ON DELETE CASCADE` rules.

Mock Test · 1 Questions · Optional

Ready for SQLite vs Other DBs?

Take a quick test to evaluate yourself, or move straight to the next module.

Intermediate1 questions

Skip