In PostgreSQL, constraints are rules applied to table columns to maintain the accuracy and reliability of data.
For example, consider a university student table.
We may want to ensure that:
We can enforce these rules using PostgreSQL constraints.
A constraint is a rule that controls what data can be stored in a table.
If someone tries to insert or update data that violates a constraint, PostgreSQL rejects the operation.
For example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 16)
);
This table has three constraints:
PRIMARY KEY: Student ID must be unique and not null.NOT NULL: Student name cannot be null.CHECK: Student age must be at least 16.PostgreSQL provides several important constraints.
| Constraint | Purpose |
|---|---|
PRIMARY KEY |
Uniquely identifies each record |
FOREIGN KEY |
Maintains relationships between tables |
UNIQUE |
Prevents duplicate values |
NOT NULL |
Prevents null values |
CHECK |
Ensures values satisfy a condition |
DEFAULT |
Provides a default value when one is not supplied |
Let’s understand each constraint with practical examples.
A primary key uniquely identifies every record in a table.
For example, a university may assign a unique student ID to each student.
Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
department VARCHAR(100)
);
Insert records:
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science'),
(2, 'Ahmed', 'Information Technology'),
(3, 'Sara', 'Mathematics');
The following insertion will fail because student ID 1 already exists:
INSERT INTO students
VALUES (1, 'Usman', 'Physics');
Important characteristics:
NULL.A composite primary key consists of two or more columns that together uniquely identify a record.
Consider a student enrollment table.
A student can enroll in multiple courses, and a course can have multiple students.
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);
Here, the combination of student_id and course_id must be unique.
The following records are allowed:
| student_id | course_id |
|---|---|
| 1 | 101 |
| 1 | 102 |
| 2 | 101 |
But this record is not allowed because the combination already exists:
INSERT INTO enrollments
VALUES (1, 101, '2026-11-09');
A foreign key establishes a relationship between two tables.
For example:
departments stores department information.students stores student information.First, create the departments table:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
Insert departments:
INSERT INTO departments
VALUES
(1, 'Computer Science'),
(2, 'Mathematics'),
(3, 'Physics');
Now create the students table:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
Insert students:
INSERT INTO students
VALUES
(101, 'Ali', 1),
(102, 'Ahmed', 2),
(103, 'Sara', 1);
The department_id in the students table must refer to an existing department.
For example, this insertion will fail if department 10 does not exist:
INSERT INTO students
VALUES (104, 'Usman', 10);
Remember: A foreign key can contain NULL unless the column also has a NOT NULL constraint.
Suppose a department has students. What happens if someone tries to delete that department?
PostgreSQL provides different foreign key actions.
| Option | Purpose |
|---|---|
RESTRICT |
Prevent deletion while referencing records exist |
NO ACTION |
Check the relationship constraint when the constraint is checked |
CASCADE |
Delete related child records automatically |
SET NULL |
Set referencing foreign key values to NULL |
SET DEFAULT |
Set referencing foreign key values to their default |
Example: ON DELETE CASCADE
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE
);
Now, deleting a department also deletes students associated with it.
DELETE FROM departments
WHERE department_id = 1;
Use carefully
ON DELETE CASCADE can delete many related records automatically.
The UNIQUE constraint prevents duplicate values in a column or combination of columns.
For example, each student should have a unique email address.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
email VARCHAR(150) UNIQUE
);
Insert records:
INSERT INTO students
VALUES
(1, 'Ali', 'ali@example.com'),
(2, 'Ahmed', 'ahmed@example.com');
This insertion will fail because the email already exists:
INSERT INTO students
VALUES
(3, 'Sara', 'ali@example.com');
Important: By default, PostgreSQL allows multiple NULL values in a UNIQUE column because NULL values are treated as distinct for uniqueness checks.
If you want to prevent null values too, combine UNIQUE with NOT NULL.
We can apply a unique constraint to a combination of columns.
For example, a student may enroll in different semesters, but should not have duplicate enrollment records for the same course in the same semester.
CREATE TABLE course_enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
semester VARCHAR(30),
UNIQUE (student_id, course_id, semester)
);
The combination of all three columns must be unique.
The NOT NULL constraint ensures that a column must contain a value.
Example:
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(100) NOT NULL,
email VARCHAR(150)
);
The following insertion will fail:
INSERT INTO teachers
(teacher_id, email)
VALUES
(1, 'teacher@example.com');
The reason is that teacher_name is required.
Important: NOT NULL prevents missing values, but it does not prevent empty strings such as ''.
The CHECK constraint ensures that data satisfies a specified condition.
For example, student age must be at least 16.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
age INT CHECK (age >= 16)
);
This insertion is allowed:
INSERT INTO students
VALUES (1, 'Ali', 20);
This insertion is rejected:
INSERT INTO students
VALUES (2, 'Ahmed', 12);
Salary must be greater than zero:
salary NUMERIC(10,2) CHECK (salary > 0)
Marks must be between 0 and 100:
marks INT CHECK (marks BETWEEN 0 AND 100)
Gender must be one of the specified values:
gender VARCHAR(10)
CHECK (gender IN ('Male', 'Female', 'Other'))
Important: A CHECK constraint passes when its expression evaluates to TRUE or NULL. Use NOT NULL as well if a value is required.
The DEFAULT constraint provides a value automatically when an INSERT statement does not supply a value for that column.
Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
admission_status VARCHAR(20) DEFAULT 'Pending'
);
Insert a student:
INSERT INTO students
(student_id, student_name)
VALUES
(1, 'Ali');
The database automatically assigns:
admission_status = Pending
We can also provide a default date:
admission_date DATE DEFAULT CURRENT_DATE
Important: A default value is used when a column is omitted from the INSERT statement or when DEFAULT is explicitly specified. Inserting NULL does not automatically use the default.
We can assign meaningful names to constraints using CONSTRAINT.
Example:
CREATE TABLE students (
student_id INT,
student_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_students
PRIMARY KEY (student_id),
CONSTRAINT uq_students_email
UNIQUE (student_name)
);
Meaningful constraint names make database maintenance easier.
A more appropriate example using an email column:
CREATE TABLE students (
student_id INT,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(150),
CONSTRAINT pk_students
PRIMARY KEY (student_id),
CONSTRAINT uq_students_email
UNIQUE (email)
);
We can add constraints to an existing table.
Suppose we already have a table:
CREATE TABLE students (
student_id INT,
student_name VARCHAR(100),
age INT
);
Now add a primary key:
ALTER TABLE students
ADD CONSTRAINT pk_students
PRIMARY KEY (student_id);
Add a unique constraint:
ALTER TABLE students
ADD CONSTRAINT uq_students_name
UNIQUE (student_name);
Add a check constraint:
ALTER TABLE students
ADD CONSTRAINT chk_students_age
CHECK (age >= 16);
Add a foreign key:
ALTER TABLE students
ADD CONSTRAINT fk_students_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);
The last command requires a department_id column to exist in the students table.
We can use ALTER TABLE to make an existing column mandatory.
ALTER TABLE students
ALTER COLUMN student_name
SET NOT NULL;
Before running this command, ensure that no existing records contain NULL in student_name.
To allow null values again:
ALTER TABLE students
ALTER COLUMN student_name
DROP NOT NULL;
We can add a default value to an existing column.
ALTER TABLE students
ALTER COLUMN age
SET DEFAULT 18;
Now, if a new record is inserted without an age value, PostgreSQL uses 18.
To remove the default:
ALTER TABLE students
ALTER COLUMN age
DROP DEFAULT;
We can remove a named constraint using DROP CONSTRAINT.
Syntax:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example:
ALTER TABLE students
DROP CONSTRAINT chk_students_age;
This removes the age check constraint.
To remove a primary key:
ALTER TABLE students
DROP CONSTRAINT pk_students;
Note: PostgreSQL automatically generates names for unnamed constraints. You can inspect the actual names before removing them.
We can use the following SQL query to view constraints in the current database:
SELECT
constraint_name,
table_name,
constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
ORDER BY table_name, constraint_name;
Common constraint types include:
| Constraint type | Meaning |
|---|---|
PRIMARY KEY |
Primary key |
FOREIGN KEY |
Foreign key |
UNIQUE |
Unique constraint |
CHECK |
Check constraint |
For a quick overview in the psql terminal:
\d students
This displays the table structure, including its constraints.
Let’s create a small university database structure using different constraints.
Step 1: Create departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL UNIQUE
);
Step 2: Insert departments
INSERT INTO departments
VALUES
(1, 'Computer Science'),
(2, 'Mathematics'),
(3, 'Physics');
Step 3: Create students table
CREATE TABLE students (
student_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
age INT CHECK (age >= 16),
department_id INT,
admission_status VARCHAR(20) DEFAULT 'Pending',
CONSTRAINT fk_students_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
Step 4: Insert student records
INSERT INTO students
(student_name, email, age, department_id)
VALUES
('Ali', 'ali@example.com', 20, 1),
('Ahmed', 'ahmed@example.com', 21, 2),
('Sara', 'sara@example.com', 19, 1);
Step 5: View student records
SELECT *
FROM students;
Step 6: Test the constraints
Try these operations separately:
-- Duplicate email
INSERT INTO students
(student_name, email, age, department_id)
VALUES
('Usman', 'ali@example.com', 22, 1);
-- Invalid age
INSERT INTO students
(student_name, email, age, department_id)
VALUES
('Usman', 'usman@example.com', 12, 1);
-- Invalid department
INSERT INTO students
(student_name, email, age, department_id)
VALUES
('Usman', 'usman@example.com', 22, 99);
Each operation should fail because it violates a different constraint.
NOT NULL when existing records contain null values.CASCADE without understanding its effects.DEFAULT replaces explicitly inserted NULL.CHECK constraint can accept NULL unless NOT NULL is also applied.students with a primary key.student_name a required column.Pending to admission status.departments table.students.department_id to departments.department_id.ALTER TABLE.NOT NULL.ON DELETE CASCADE to an appropriate relationship.ON DELETE RESTRICT and ON DELETE SET NULL.information_schema.UNIQUE and PRIMARY KEY are different.PRIMARY KEY and UNIQUE?NOT NULL do?CHECK?DEFAULT?ON DELETE CASCADE and ON DELETE SET NULL?| Constraint | Main purpose |
|---|---|
PRIMARY KEY |
Unique record identification |
FOREIGN KEY |
Table relationship |
UNIQUE |
Prevent duplicate values |
NOT NULL |
Require a value |
CHECK |
Validate a condition |
DEFAULT |
Provide a default value |
Important SQL commands:
-- Add a constraint
ALTER TABLE students
ADD CONSTRAINT chk_age CHECK (age >= 16);
-- Remove a constraint
ALTER TABLE students
DROP CONSTRAINT chk_age;
-- Set NOT NULL
ALTER TABLE students
ALTER COLUMN student_name SET NOT NULL;
-- Set DEFAULT
ALTER TABLE students
ALTER COLUMN admission_status SET DEFAULT 'Pending';
Next Lesson: PostgreSQL Views in Depth
Students will learn how to create views, update views, replace views, delete views, and use views to simplify complex SQL queries.