In PostgreSQL, constraints are rules applied to table columns to control the type of data that can be stored.
Constraints help maintain accurate, valid, and reliable data in a database.
For example:
These rules can be implemented using constraints.
PostgreSQL provides several commonly used constraints:
| Constraint | Purpose |
|---|---|
NOT NULL |
Prevents a column from storing NULL values. |
UNIQUE |
Prevents duplicate values. |
PRIMARY KEY |
Uniquely identifies each row. |
FOREIGN KEY |
Links records between tables. |
CHECK |
Ensures values meet a specified condition. |
DEFAULT |
Provides a default value when no value is supplied. |
The NOT NULL constraint ensures that a column must have a value.
Real-life example: Every student must have a name.
CREATE TABLE students (
student_id INTEGER,
student_name VARCHAR(100) NOT NULL
);
In this example:
student_id can contain a NULL value.student_name cannot contain a NULL value.Example:
INSERT INTO students (student_id, student_name)
VALUES (1, 'Ali');
This query works successfully.
The following query produces an error because the student name is missing:
INSERT INTO students (student_id, student_name)
VALUES (2, NULL);
The UNIQUE constraint ensures that values in a column are not duplicated.
Real-life example: Every student should have a different email address.
CREATE TABLE students (
student_id INTEGER,
email VARCHAR(100) UNIQUE
);
Example:
INSERT INTO students (student_id, email)
VALUES (1, 'ali@example.com');
INSERT INTO students (student_id, email)
VALUES (2, 'ahmad@example.com');
Both records can be inserted because their email addresses are different.
However, inserting the same email address again will produce an error.
INSERT INTO students (student_id, email)
VALUES (3, 'ali@example.com');
Important: PostgreSQL allows multiple NULL values in a column with a regular UNIQUE constraint.
A PRIMARY KEY uniquely identifies every record in a table.
For example, every student has a unique student ID.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100)
);
Important points:
Example:
INSERT INTO students (student_id, student_name)
VALUES (1, 'Ali');
INSERT INTO students (student_id, student_name)
VALUES (2, 'Ahmad');
Both records are valid because their IDs are different.
The following query produces an error because the ID is duplicated:
INSERT INTO students (student_id, student_name)
VALUES (1, 'Usman');
A FOREIGN KEY connects two tables and ensures that a value in one table refers to an existing record in another table.
Real-life example: A student belongs to a department. The department must already exist in the university database.
First, create the departments table:
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100)
);
Insert some departments:
INSERT INTO departments
VALUES
(1, 'Computer Science'),
(2, 'Information Technology'),
(3, 'Mathematics');
Now create the students table:
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
department_id INTEGER REFERENCES departments(department_id)
);
Insert a student:
INSERT INTO students
VALUES (101, 'Ali', 1);
This query works because department ID 1 exists in the departments table.
The following query produces an error because department ID 5 does not exist:
INSERT INTO students
VALUES (102, 'Ahmad', 5);
Important: A foreign key helps maintain referential integrity between related tables.
The CHECK constraint ensures that values satisfy a specified condition.
Real-life example: A student’s age must be at least 16.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
age INTEGER CHECK (age >= 16)
);
Example:
INSERT INTO students
VALUES (1, 'Ali', 20);
This query works because the age is greater than or equal to 16.
The following query produces an error:
INSERT INTO students
VALUES (2, 'Ahmad', 12);
The age does not satisfy the condition.
You can also apply a CHECK constraint to fees:
fee NUMERIC(10, 2) CHECK (fee >= 0)
This ensures that the fee cannot be negative.
The DEFAULT constraint automatically provides a value when a user does not supply one during insertion.
Real-life example: Every new student is considered active by default.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
is_active BOOLEAN DEFAULT TRUE
);
Example:
INSERT INTO students (student_id, student_name)
VALUES (1, 'Ali');
The database automatically assigns TRUE to is_active.
To view the record:
SELECT * FROM students;
You can also provide a different value explicitly:
INSERT INTO students
VALUES (2, 'Ahmad', FALSE);
Let’s create a students table using multiple constraints.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
age INTEGER CHECK (age >= 16),
fee NUMERIC(10, 2) DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE
);
Insert student records:
INSERT INTO students
(student_id, student_name, email, age, fee)
VALUES
(1, 'Ali', 'ali@example.com', 20, 50000),
(2, 'Ahmad', 'ahmad@example.com', 22, 45000);
Display all records:
SELECT * FROM students;
This table uses five constraints:
PRIMARY KEY for student identification.NOT NULL for the student name.UNIQUE for the email address.CHECK for age validation.DEFAULT for fee and active status.Constraints can also be added after creating a table using ALTER TABLE.
For example, add a UNIQUE constraint to the email column:
ALTER TABLE students
ADD CONSTRAINT unique_student_email UNIQUE (email);
Add a CHECK constraint:
ALTER TABLE students
ADD CONSTRAINT check_student_age CHECK (age >= 16);
Note: These commands will fail if existing records violate the new constraints.
Exercise 1: Create a Students Table
Create a table named students with these columns:
student_id — INTEGERstudent_name — VARCHAR(100)email — VARCHAR(100)age — INTEGERApply the following constraints:
student_id should be the PRIMARY KEY.student_name should be NOT NULL.email should be UNIQUE.age must be greater than or equal to 16.Exercise 2: Create a Courses Table
Create a table named courses with:
course_id — INTEGER PRIMARY KEYcourse_name — VARCHAR(100) NOT NULLcredit_hours — INTEGER CHECK (credit_hours > 0)course_fee — NUMERIC(10, 2) DEFAULT 0Exercise 3: Create Related Tables
Create two tables:
departmentsteachersApply a PRIMARY KEY to each table and use a FOREIGN KEY in the teachers table to reference the departments table.
Exercise 4: Test Constraints
Try inserting:
Observe which queries work and which produce errors.
| Constraint | Remember |
|---|---|
| NOT NULL | Value is required. |
| UNIQUE | No duplicate non-NULL values. |
| PRIMARY KEY | Unique identification of each row. |
| FOREIGN KEY | Connects related tables. |
| CHECK | Validates a condition. |
| DEFAULT | Supplies a value automatically. |
Next Lesson: INSERT INTO Statement in PostgreSQL
Students will learn how to insert single and multiple records into database tables.