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

A foreign key establishes and enforces a link between data in two tables. It prevents actions that would destroy links between tables.

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)
);

Foreign key constraints ensure referential integrity by preventing:

• Insertion of records with non-existent foreign key values
• Deletion of referenced records (unless CASCADE is specified)