What is a foreign key and how does it work in PostgreSQL?

A foreign key is a column whose values must match a row in another table. PostgreSQL rejects any change that would break that link, which is called referential integrity.

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INTEGER REFERENCES departments(dept_id)
);

Here every employees.dept_id must name an existing department or be NULL. PostgreSQL enforces the link in three ways.

• An INSERT or UPDATE that sets a dept_id with no matching department fails.
• Deleting a department that employees still reference fails by default, which is ON DELETE NO ACTION.
• ON DELETE CASCADE deletes the referencing employees instead, and ON DELETE SET NULL sets their dept_id to NULL.

The referenced column must be a primary key or have a unique constraint. PostgreSQL does not index the referencing column for you, so a delete from departments must search employees unless you index employees.dept_id.