Data that points at data
An order belongs to a customer. Instead of copying the customer's name into every order, the order stores the customer's id — a foreign key. The database then guarantees every order points to a real customer.
REFERENCES
CREATE TABLE authors (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE posts (
id serial PRIMARY KEY,
author_id int NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
title text NOT NULL
);
INSERT INTO authors (name) VALUES ('Asha'), ('Bikash');
INSERT INTO posts (author_id, title) VALUES (1, 'Momo secrets'), (1, 'Achar 101'), (2, 'Tea time');
SELECT * FROM posts;
INSERT INTO posts (author_id, title) VALUES (99, 'Ghost post');- ON DELETE CASCADE: deleting an author deletes their posts. Default (NO ACTION) blocks the delete while posts exist. SET NULL clears the link.
- Name foreign key columns <thing>_id.
- Index foreign key columns you join on often (Unit 3).