Constraints and Foreign Keys

Constraints are rules the database enforces on every change, whatever program or person makes it. They're the most reliable way to keep data valid.

Foreign keys

A foreign key stops orphaned data — an order for a customer that doesn't exist:

PRAGMA foreign_keys = ON;
INSERT INTO orders (customer_id, ordered_on) VALUES (99, '2025-05-01');

Error near line 2: FOREIGN KEY constraint failed

SQLite only enforces foreign keys when you turn them on with PRAGMA foreign_keys = ON (once per connection). PostgreSQL, MySQL (InnoDB), and SQL Server always enforce them.

It also stops you deleting a row that others still refer to:

PRAGMA foreign_keys = ON;
DELETE FROM customers WHERE id = 1;

Error near line 2: FOREIGN KEY constraint failed

What happens on delete: ON DELETE

You choose what happens to referring rows when the referenced row is deleted:

-- standalone
PRAGMA foreign_keys = ON;

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

INSERT INTO authors VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO posts (author_id, title) VALUES (1, 'Engines'), (1, 'Notes'), (2, 'Kernels');

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

+----+-----------+---------+
| id | author_id |  title  |
+----+-----------+---------+
|  3 |         2 | Kernels |
+----+-----------+---------+

Use CASCADE for data that has no meaning without its parent (a post's comments); keep the default for important records (a customer's orders).

Adding constraints later

In PostgreSQL and MySQL, ALTER TABLE ... ADD CONSTRAINT adds a constraint to an existing table — after cleaning any rows that violate it. (SQLite's ALTER TABLE is more limited; changing constraints there means recreating the table.)

Common mistakes

• Relying only on application code to keep data valid. Bugs, scripts, and manual fixes bypass the application — constraints can't be bypassed.
• Forgetting PRAGMA foreign_keys = ON in SQLite, and assuming foreign keys are being checked.