Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL

Introduction

Column constraints help keep your data clean and correct by setting simple rules on what values can be stored in each column.

When you want to make sure a column always has a value (NOT NULL).

When you want to prevent duplicate values in a column (UNIQUE).

When you want to allow only certain values based on a condition (CHECK).

When you are designing a table to store user information and need to enforce rules.

When you want to avoid mistakes like empty or invalid data in your database.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name data_type CONSTRAINT constraint_name constraint_type,
  ...
);

-- Examples of constraint_type:
-- NOT NULL
-- UNIQUE
-- CHECK (condition)

Constraints are added when creating or altering a table.

CHECK constraints use a condition to allow only valid values.

Examples

This creates a users table where email cannot be empty and must be unique, and age must be 18 or older.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  age INT CHECK (age >= 18)
);

Here, product name cannot be empty and price must be greater than zero.

PostgreSQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC CHECK (price > 0)
);

Sample Program

This creates an employees table with constraints: name cannot be empty, email must be unique if given, and salary must be at least 30000. The last insert will fail because salary is less than 30000.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  salary NUMERIC CHECK (salary >= 30000)
);

INSERT INTO employees (name, email, salary) VALUES
('Alice', '[e-mail]', 50000),
('Bob', NULL, 40000),
('Charlie', '[e-mail]', 25000);

Important Notes

NOT NULL means the column must have a value; it cannot be empty.

UNIQUE means no two rows can have the same value in that column.

CHECK lets you define a rule that values must follow, like a minimum or maximum.

Summary

Use NOT NULL to require a value in a column.

Use UNIQUE to prevent duplicate values.

Use CHECK to enforce custom rules on column values.