A PRIMARY KEY column (or combination of columns) must be unique and non-null for every row, and a table can have only one. A FOREIGN KEY column in one table references a primary key in another, and the database enforces referential integrity: it rejects an insert or update that would create a foreign key value with no matching primary key, and by default rejects deleting a referenced row that still has dependents — unless an ON DELETE behavior says otherwise.
PRIMARY KEY: uniquely identifying a row
Most engines auto-increment an integer primary key by default, so you rarely specify the id value yourself on insert.
CREATE TABLE customers (
id INTEGER PRIMARY KEY, -- auto-increments in most engines
name VARCHAR(100) NOT NULL
);
INSERT INTO customers (name) VALUES ('Alice'); -- id is assigned automaticallyFOREIGN KEY: enforcing that a relationship exists
Without this constraint, nothing would stop orders.customer_id from referencing a customer that was deleted or never existed — the FOREIGN KEY makes that state impossible at the database level, not just something the application is supposed to prevent.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total NUMERIC(10, 2)
);
INSERT INTO orders (customer_id, total) VALUES (999, 50.00);
-- ERROR: violates foreign key constraint — no customer with id 999 existsON DELETE behavior: what happens to dependents
ON DELETE CASCADE deletes dependent rows automatically when the referenced row is deleted; ON DELETE SET NULL clears the foreign key instead; ON DELETE RESTRICT (often the default) blocks the delete entirely while dependents exist. The right choice depends on the relationship — deleting a customer should probably cascade to their orders, but deleting a product category probably shouldn't silently delete every product in it.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);
-- deleting a customer now automatically deletes all their orders too