Constraints & Data Integrity
1/3

Primary & Foreign Keys

Constraints are rules that PostgreSQL enforces on your data. They prevent invalid data from being inserted and maintain the logical relationships between tables. The two most fundamental constraints are PRIMARY KEY and FOREIGN KEY, which together form the backbone of relational database design.

Primary Keys

A primary key uniquely identifies each row in a table. It enforces two rules simultaneously:

  1. Uniqueness -- no two rows can have the same primary key value
  2. Not null -- the primary key column cannot contain NULL

Declaring a Primary Key

You can define a primary key inline with the column definition or as a separate table constraint.

Inline syntax:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

Table constraint syntax:

CREATE TABLE students (
  id INTEGER,
  name TEXT NOT NULL,
  email TEXT,
  PRIMARY KEY (id)
);

Auto-Incrementing Primary Keys

In practice, you rarely assign primary key values manually. PostgreSQL provides auto-incrementing types:

Using SERIAL (traditional approach):

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

SERIAL is shorthand for creating an integer column backed by a sequence. PostgreSQL automatically generates the next value when you insert a row without specifying the id.

Using GENERATED ALWAYS (modern approach, SQL standard):

CREATE TABLE students (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

The GENERATED ALWAYS AS IDENTITY approach is preferred in modern PostgreSQL because it follows the SQL standard and provides stricter guarantees.

Composite Primary Keys

A primary key can span multiple columns. This is common in junction tables (also called bridge tables or associative tables) that represent many-to-many relationships:

CREATE TABLE student_courses (
  student_id INTEGER,
  course_id INTEGER,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

With this composite key, the combination of student_id and course_id must be unique. A student can appear in multiple rows (for different courses), and a course can appear in multiple rows (for different students), but the same student-course pair cannot appear twice.

What Happens When You Violate a Primary Key

INSERT INTO students (id, name) VALUES (1, 'Alice');
INSERT INTO students (id, name) VALUES (1, 'Bob');
-- ERROR: duplicate key value violates unique constraint "students_pkey"
-- DETAIL: Key (id)=(1) already exists.

INSERT INTO students (id, name) VALUES (NULL, 'Charlie');
-- ERROR: null value in column "id" violates not-null constraint

Foreign Keys

A foreign key creates a link between two tables. It ensures that a value in one table (the child) must match an existing value in another table (the parent). This is called referential integrity.

Declaring a Foreign Key

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id)
);

The REFERENCES departments(id) clause means that every value in employees.department_id must correspond to an existing departments.id value (or be NULL).

You can also use the table constraint syntax for more complex foreign keys:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

Named Constraints

Naming your constraints makes error messages clearer and makes it easier to manage constraints later:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  CONSTRAINT fk_employee_department
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

What Happens When You Violate a Foreign Key

If you try to insert a child row that references a non-existent parent:

INSERT INTO employees (name, department_id) VALUES ('Alice', 999);
-- ERROR: insert or update on table "employees" violates foreign key constraint
-- DETAIL: Key (department_id)=(999) is not present in table "departments".

If you try to delete a parent row that has children referencing it:

DELETE FROM departments WHERE id = 1;
-- ERROR: update or delete on table "departments" violates foreign key constraint
-- DETAIL: Key (id)=(1) is still referenced from table "employees".

ON DELETE Actions

When a parent row is deleted, you can specify what should happen to the child rows. This is controlled by the ON DELETE clause.

ON DELETE RESTRICT (default)

The delete is blocked if any child rows reference the parent. This is the default behavior.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id) ON DELETE RESTRICT
);

ON DELETE CASCADE

Automatically delete all child rows when the parent is deleted. The deletion cascades through the relationship.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id) ON DELETE CASCADE
);

-- Deleting a department now also deletes all its employees
DELETE FROM departments WHERE id = 1;
-- All employees with department_id = 1 are also deleted

Use CASCADE when child rows have no meaning without their parent. For example, deleting an order should delete its order line items.

ON DELETE SET NULL

Set the foreign key column to NULL when the parent is deleted. The child rows are preserved but their reference becomes NULL.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
);

-- Deleting a department sets employees' department_id to NULL
DELETE FROM departments WHERE id = 1;
-- Employees previously in department 1 now have department_id = NULL

Use SET NULL when child rows should survive but lose their association. For example, an employee whose manager is removed from the system might have their manager_id set to NULL.

ON DELETE SET DEFAULT

Sets the foreign key column to its default value when the parent is deleted:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER DEFAULT 0 REFERENCES departments(id) ON DELETE SET DEFAULT
);

ON UPDATE CASCADE

You can also specify behavior for when a parent's primary key is updated:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

With ON UPDATE CASCADE, if a department's id changes, all employees referencing that department are automatically updated to the new value.

Self-Referencing Foreign Keys

A table can have a foreign key that references itself. This is common for hierarchical data like organizational charts:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id) ON DELETE SET NULL
);

Here, manager_id points to another row in the same employees table. The top-level manager has manager_id = NULL.

Composite Foreign Keys

Foreign keys can reference composite primary keys:

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  grade CHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id, course_id) REFERENCES student_courses(student_id, course_id)
);

Inspecting Existing Constraints

You can view existing constraints on a table:

-- List all constraints on a table
SELECT
  conname AS constraint_name,
  contype AS constraint_type
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;

Constraint types: p = primary key, f = foreign key, u = unique, c = check.

Best Practices

  1. Every table should have a primary key. Even if you do not think you need one now, primary keys are essential for updates, deletes, and joins.

  2. Use surrogate keys (auto-incrementing IDs) for most tables. Natural keys (like email or SSN) can change or have format issues. Surrogate keys are stable and simple.

  3. Always name your constraints. Named constraints produce clearer error messages and are easier to modify later.

  4. Choose ON DELETE behavior carefully. CASCADE can silently delete large amounts of data. Prefer RESTRICT when in doubt -- it is safer to fail loudly than to delete data accidentally.

  5. Index foreign key columns. PostgreSQL does not automatically create an index on the child side of a foreign key. Adding one speeds up joins and cascading deletes significantly.

Try It Yourself

Think about how you would design these relationships:

  1. A books table and an authors table where each book has exactly one author. What happens when an author is deleted?

  2. A many-to-many relationship between students and courses using a junction table. What should the primary key of the junction table be?

  3. An employees table where each employee can have a manager (who is also an employee). How would you handle deleting a manager?

Take Quiz Next Lesson