Keys and relationships

Why keys?

Two customers can share a name. Two Ravi Kumars in Delhi is not unusual. To point at exactly one row you need a value that never repeats. That value is a key, and keys are what let tables link to each other. This linking is the "relational" in relational database, an idea introduced by E. F. Codd at IBM in 1970.

Primary key

A primary key is a column (or a group of columns) whose value is unique for every row and never empty. A table has at most one primary key. In SHOP, customers.id, products.id and orders.id are primary keys. Order 104 means one order and nothing else.

Natural and surrogate keys

A natural key is a value from the real world that is already unique: a vehicle registration number, a bank IFSC code, a train number. A surrogate key is a number the database makes up only to be the key, like id 1, 2, 3.

• Natural keys mean something, but they can change (people change email addresses and phone numbers), and some are private. An Aadhaar number should never be a key that is copied into many tables.
• Surrogate keys never change and are small and fast. Most apps use them, and keep natural values in normal columns with a UNIQUE rule.

Composite keys

Sometimes one column is not enough. In order_items, an order can have many products and a product can appear in many orders, but the pair (order_id, product_id) appears only once. So the primary key is both columns together. A key made of more than one column is a composite key.

SELECT order_id, product_id, qty, price FROM order_items WHERE order_id = 101;
-- 101 | 1 | 2 | 650
-- 101 | 2 | 3 | 160

Foreign keys

A foreign key is a column whose values must match a primary key in another table. orders.customer_id is a foreign key that points to customers.id. Instead of copying Asha's name and city into every order, the order stores her id, 1. Her details live in one place, so changing her city is one update, not fifty.

SELECT id, order_date, status FROM orders WHERE customer_id = 1;
-- 101 | 2025-01-05 | Delivered
-- 103 | 2025-02-14 | Delivered
-- 108 | 2025-04-02 | Placed

Three kinds of relationship

A junction table (also called a link or bridge table) sits between two tables and holds one row per pairing. order_items says "order 101 contains product 1" and can also store details of that pairing, like qty and the price paid.

An ER diagram in words

An ER (entity-relationship) diagram is a drawing of tables as boxes and relationships as lines. You can also write it in words:

customers (id PK) 1 ──< many  orders (id PK, customer_id FK)
orders    (id PK) 1 ──< many  order_items (order_id FK, product_id FK; PK = both)
products  (id PK) 1 ──< many  order_items
employees (id PK) 1 ──< many  employees (manager_id FK)   -- a table can point to itself

Referential integrity, and the SQLite switch

Referential integrity means every foreign key value points to a row that really exists. An order for customer 99, when there is no customer 99, is an orphan row. The database can block orphans for you, but SQLite does this only after you switch it on, once per connection.

PRAGMA foreign_keys = ON;
INSERT INTO orders VALUES (109, 99, '2025-04-10', 'Placed');
-- Error: FOREIGN KEY constraint failed

Common mistakes

• Forgetting PRAGMA foreign_keys = ON in SQLite, then finding orphan rows months later. Turn it on whenever your app opens a connection.
• Storing many ids in one column, like products = '1,5,7'. You cannot join or check such a list. Use a junction table with one row per pair.
• Using a name as the key. Names repeat and change. Use an id, and keep the name in its own column.
• Giving the foreign key a different type from the key it points to (TEXT vs INTEGER). Keep the types the same.