DataslopeForeign Keys
Learn how SQLite foreign keys connect rows across tables and protect references when enforcement is enabled.
A foreign key is a column that points from one table to a row in another table. It is how a row says, "I belong with that row over there."
If customers.id is the primary key, then orders.customer_id can store one of those ids as a foreign key.
The pointer idea
In this picture, order 101 does not copy Ada's whole customer record. It stores customer_id = 1, which points to customers.id = 1.
This keeps data tidy:
- Ada's name lives in one place.
- Many orders can point to the same customer.
- If Ada's name changes, the orders still point to customer
1.
The phrase REFERENCES customers(id) declares the relationship: each orders.customer_id is meant to refer to a value in customers.id.
In the orders table, what does customer_id = 1 mean?
The order's own id is 1.
The order points to the customer whose id is 1.
The customer table has exactly one row.
The order amount is 1.
Referential integrity
Referential integrity means references should not dangle. If an order says customer_id = 99, customer 99 should actually exist.
Important SQLite note: enforcement must be turned on
SQLite can enforce foreign keys, but enforcement is controlled by PRAGMA foreign_keys. In many SQLite environments it is off by default for each database connection. To rely on foreign-key protection, turn it on before changing data:
PRAGMA foreign_keys = ON;Turn it on for real protection
Declaring `REFERENCES` describes the relationship. In SQLite, `PRAGMA foreign_keys = ON;` makes SQLite reject rows that break the relationship.
The second insert is blocked. SQLite is protecting you from an orphan row: an order that claims to belong to a missing customer.
Seeing the links
The join follows the foreign-key arrows and shows readable customer names next to order facts.
Check your understanding
What is a foreign key?
A column that must contain a different value in every row of its own table.
A column that stores the primary key value of a related row in another table.
A special password for opening a table.
A column that stores all rows from another table.
In SQLite, what must you do if you want foreign-key constraints to be enforced?
Rename every foreign-key column to id.
Run PRAGMA foreign_keys = ON; for the connection.
Use only text primary keys.
Avoid primary keys completely.
Why is customer_id better than copying customer_name into every order?
Customer names cannot be stored in SQLite.
The id is stable and unique, while the customer's details can stay in one row.
Foreign keys make every query shorter.
It prevents a customer from having more than one order.
What is an orphan order?
An order with two valid customers.
An order with a high amount.
An order whose customer_id points to a customer row that does not exist.
A customer with no orders.