One-to-One, One-to-Many, Many-to-Many
A shop keeps customers, orders and products in separate tables, so each fact is stored once. Keys link them: a foreign key in one table points to the primary key of another.
Syntax
Where the key lives decides the kind of relationship:
Code-- One-to-many: the "many" side holds the foreign key CREATE TABLE child (child_id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent (parent_id)); -- One-to-one: the foreign key is also UNIQUE CREATE TABLE detail (detail_id INTEGER PRIMARY KEY, parent_id INTEGER UNIQUE REFERENCES parent (parent_id)); -- Many-to-many: a junction table with a key on the pair CREATE TABLE a_b (a_id INTEGER REFERENCES a (a_id), b_id INTEGER REFERENCES b (b_id), PRIMARY KEY (a_id, b_id));
| Relationship | Lab example | How it's built |
|---|---|---|
| One-to-many | a customer has many orders | orders.customer_id references customers |
| One-to-one | a customer has at most one wallet | accounts.customer_id references customers and is UNIQUE |
| Many-to-many | an order has many products, a product is in many orders | the junction table order_items |
Examples
One-to-many
Follow the foreign key from one customer to their many orders.
CodeSELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 7 ORDER BY order_id;
One-to-one
A UNIQUE foreign key allows only one row per customer.
CodeSELECT account_no, balance FROM accounts WHERE customer_id = 3; -- Fails: customer 3 already has a wallet INSERT INTO accounts (customer_id, account_no, balance) VALUES (3, 'ACC999999', 0);
Many-to-many
Each row of the junction table links one order to one product.
CodeSELECT order_id, product_id, quantity FROM order_items WHERE order_id = 2; -- products in one order SELECT order_id, quantity FROM order_items WHERE product_id = 19; -- orders of one product
Reading the keys
pg_constraint, PostgreSQL's own list of rules, shows every key of a table: p primary key,
u unique, f foreign key, c check.
CodeSELECT conname, contype, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE conrelid = 'order_items'::regclass AND contype IN ('p', 'f') ORDER BY conname;
Lists hiding in a column
products.tags stores a list, which is a many-to-many in disguise. PostgreSQL allows such array
columns, and unnest turns the list into rows; most databases have no list type at all.
CodeSELECT product_id, unnest(tags) AS tag FROM products WHERE product_id = 3; CREATE TABLE product_bundles (bundle_id INTEGER, product_id INTEGER REFERENCES products (product_id), PRIMARY KEY (bundle_id, product_id));
Remember
- The foreign key lives on the "many" side.
- A UNIQUE foreign key makes the link one-to-one.
- A junction table needs a key on the pair, so the same link can't be stored twice.
- A list in one column is a hidden many-to-many: hard to filter and impossible to constrain.
Practice
- The Lines of Order 1 (Easy)
- What Makes Accounts One-to-One (Easy)
- Build a Product–Tag Junction Table (Medium)