Constraints

Constraints are rules that PostgreSQL enforces on your data. They ensure that the data in your database stays accurate and consistent. Think of them as guardrails that prevent bad data from getting into your tables.

NOT NULL

The most basic constraint. It prevents a column from having empty values:

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL
);

If you try to insert a row without a name or email, PostgreSQL will reject it with an error. Simple but effective.

UNIQUE

Ensures all values in a column are different:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

No two users can have the same username or email. If you try to insert a duplicate, PostgreSQL will throw an error. You can also combine multiple columns into a unique constraint:

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  UNIQUE(student_id, course_id)
);

This ensures a student cannot enroll in the same course twice.

PRIMARY KEY

A primary key uniquely identifies each row in a table. It is essentially a combination of NOT NULL and UNIQUE:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2)
);

Every table should have a primary key. The id SERIAL PRIMARY KEY pattern is the most common approach — it gives you an auto-incrementing integer ID.

You can also create a composite primary key from multiple columns:

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  PRIMARY KEY(order_id, product_id)
);

FOREIGN KEY

Foreign keys link two tables together. They ensure that a value in one table matches a value in another table:

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

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

The REFERENCES departments(id) part means that department_id must match an existing department. You cannot add an employee to a department that does not exist.

You can also define what happens when the referenced row is deleted:

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

• ON DELETE SET NULL — Set the foreign key to NULL if the referenced row is deleted
• ON DELETE CASCADE — Delete this row if the referenced row is deleted
• ON DELETE RESTRICT — Prevent deletion of the referenced row
• ON UPDATE CASCADE — Update this column if the referenced row's key changes

CHECK

A CHECK constraint validates that values in a column meet a specific condition:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price > 0),
  stock INTEGER CHECK (stock >= 0),
  rating NUMERIC(3, 2) CHECK (rating >= 0 AND rating <= 5)
);

These ensure that prices are positive, stock is never negative, and ratings stay between 0 and 5. The CHECK constraint is very flexible — you can write any boolean expression.

Default Values

While not technically a constraint, DEFAULT works alongside constraints to provide sensible values when none is specified:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  is_published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  view_count INTEGER DEFAULT 0
);

Defaults make your life easier by automatically filling in common values so you do not have to specify them in every INSERT statement.