Data Modelling - Constraints in PostgreSQL

Introduction

Constraints in PostgreSQL are used to specify rules for data in a table. They help enforce data integrity and ensure accuracy and reliability of the data. This tutorial will cover the various types of constraints supported by PostgreSQL and provide examples of how to use them.

Primary Key Constraint

A primary key constraint uniquely identifies each record in a table. A table can have only one primary key, which can consist of single or multiple columns. Here’s an example:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);
                    

CREATE TABLE
                    

In this example, employee_id is the primary key for the employees table.

Foreign Key Constraint

A foreign key constraint is used to link two tables together. It ensures that the value in one table matches a value in another table. Here’s an example:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);
                    

CREATE TABLE
CREATE TABLE
                    

In this example, department_id in the employees table is a foreign key that references department_id in the departments table.

Unique Constraint

A unique constraint ensures that all values in a column are distinct. It can be applied to single or multiple columns. Here’s an example:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) UNIQUE
);
                    

CREATE TABLE
                    

In this example, product_name must be unique for each record in the products table.

Not Null Constraint

A not null constraint ensures that a column cannot have a NULL value. Here’s an example:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);
                    

CREATE TABLE
                    

In this example, the order_date and customer_id columns must have a value for each record in the orders table.

Check Constraint

A check constraint ensures that all values in a column satisfy a specific condition. Here’s an example:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT CHECK (age >= 18)
);
                    

CREATE TABLE
                    

In this example, the age column must have a value greater than or equal to 18 for each record in the employees table.

Default Constraint

A default constraint provides a default value for a column when no value is specified. Here’s an example:

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(100),
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
                    

CREATE TABLE
                    

In this example, the created_on column will default to the current timestamp if no value is provided.

Querying Constraints

You can query the information schema to list the constraints on a table. Here’s an example:

SELECT constraint_name, constraint_type, table_name, column_name 
FROM information_schema.constraint_column_usage 
JOIN information_schema.table_constraints 
USING (constraint_catalog, constraint_schema, constraint_name, table_name) 
WHERE table_name = 'employees';
                    

 constraint_name | constraint_type | table_name | column_name 
-----------------+-----------------+------------+-------------
 employees_pkey  | PRIMARY KEY     | employees  | employee_id
 employees_age_check | CHECK       | employees  | age
(2 rows)
                    

This query lists the constraints on the employees table.