Foreign Keys

Foreign keys enforce referential integrity.

They ensure child rows reference valid parent rows.

Core Concepts

flowchart LR
  A[Insert child row] --> B{Parent key exists?}
  B -->|Yes| C[Accept insert]
  B -->|No| D[Reject insert]

Concept	Example
Parent key	customers.customer_id
Child reference	orders.customer_id
FK clause	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Enable Checks

-- Enable foreign key enforcement for this connection.
PRAGMA foreign_keys = ON;

-- Verify current setting.
PRAGMA foreign_keys;

foreign_keys
1

Define FK

CREATE TABLE customers_fk (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders_fk (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers_fk(customer_id)
);

Expected output
Parent and child tables created with FK relationship.

Code Examples

-- Valid insert sequence: parent first, then child.
INSERT INTO customers_fk (customer_id, name)
VALUES (1, 'Ada');

INSERT INTO orders_fk (order_id, customer_id, total)
VALUES (1001, 1, 59.00);

Expected output
Both inserts succeed.

-- Invalid child reference (if foreign_keys=ON).
INSERT INTO orders_fk (order_id, customer_id, total)
VALUES (1002, 999, 10.00);

Expected output
Error: foreign key constraint failed.

SQLite-Specific Nuances

SQLite Nuance

In SQLite, foreign key enforcement is connection-specific and controlled by PRAGMA foreign_keys.

Set it to ON in every application connection unless you have a deliberate reason not to.

Common Pitfalls / Best Practices

Pitfall

Declaring foreign keys in schema but forgetting to enable enforcement, leading to orphaned rows.

Best Practice

As part of app startup, execute PRAGMA foreign_keys = ON; and verify with PRAGMA foreign_keys; in tests.

Quick Challenge

Write SQL to enable foreign key checks and confirm they are active.

View Solution

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

foreign_keys
1