PostgreSQL Constraints: NOT NULL, UNIQUE, CHECK Explained

What are PostgreSQL constraints? Constraints are rules you attach to a table so the database refuses bad data. A column can be told 'never empty' (NOT NULL), 'no duplicates' (UNIQUE), 'must pass a test' (CHECK) or 'use this value if none is given' (DEFAULT). In this beginner guide you will learn each one with small examples you can paste into psql.

You should already know how to create a table and insert rows. If not, read the earlier guides first. A table is like a spreadsheet, a column is one field (like email), and a row is one record (like one person).

Why constraints matter

Imagine a users table where two people share one email, or a price is -50. Your app code might catch some of this, but the database is the last line of defence. A constraint works every time, no matter which program sends the data.

NOT NULL: a value is required

NULL means 'no value'. By default any column may be NULL. Add NOT NULL to force a value.

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

Line 1 creates the table. Line 2 makes an id that counts up by itself and identifies each row. Lines 3 and 4 say name and email can never be empty.

INSERT INTO users (name) VALUES ('Ana');

This fails with an error like 'null value in column email violates not-null constraint'. That is the constraint doing its job: we gave no email.

UNIQUE: no duplicates

UNIQUE means no two rows may hold the same value in that column. It is perfect for emails and usernames.

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

ALTER TABLE changes an existing table. ADD CONSTRAINT gives the rule a name (users_email_unique) so error messages are easy to read. UNIQUE (email) says the email column must not repeat.

INSERT INTO users (name, email) VALUES ('Ana', 'ana@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'ana@example.com');

The first line works. The second fails with 'duplicate key value violates unique constraint' because the email is already used.

CHECK: a rule you write yourself

CHECK lets you write a true-or-false test. A row is accepted only when the test is true.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2) CHECK (price >= 0),
  stock INTEGER CHECK (stock >= 0)
);

NUMERIC(10,2) stores money-like numbers with two decimals. CHECK (price >= 0) rejects negative prices. CHECK (stock >= 0) rejects negative stock.

INSERT INTO products (name, price, stock) VALUES ('Pen', -1, 10);

This fails with 'violates check constraint'. Change -1 to 1.50 and it succeeds.

DEFAULT: a fallback value

DEFAULT is not a rule that rejects data. It fills in a value when you leave the column out.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO orders DEFAULT VALUES;
SELECT * FROM orders;

status becomes 'pending' and created_at becomes the current time via NOW(). INSERT INTO orders DEFAULT VALUES uses every default. SELECT * shows the row so you can check.

Constraints at a glance

Removing a constraint

ALTER TABLE users DROP CONSTRAINT users_email_unique;

DROP CONSTRAINT removes a rule by its name. This is why naming constraints is useful. To see all columns and rules of a table in psql, type \d users.

Common beginner mistakes

• Adding NOT NULL to a column that already has empty rows. Fix the empty rows first.
• Adding UNIQUE when duplicates already exist. Remove duplicates first.
• Thinking NULL equals an empty string. They are different things.
• Relying only on app code. Keep the rules in the database too.