PostgreSQL Constraints Explained (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL)

Constraints in PostgreSQL are used to control the data that can be inserted into tables.
They help maintain data integrity and accuracy inside the database.

In this article, you will learn:

• What constraints are
• Why constraints are important
• Common PostgreSQL constraints
• Examples of each constraint

This guide is written for beginners.

---

What Are Constraints in PostgreSQL?

A constraint is a rule applied to a table column.

Constraints ensure that:

• Invalid data is not stored
• Duplicate or incorrect data is prevented
• Relationships between tables are maintained

PostgreSQL checks constraints automatically when data is inserted or updated.

---

Why Are Constraints Important?

Constraints help:

• Prevent bad data
• Enforce unique values
• Maintain relationships between tables
• Improve database reliability

Without constraints, data quality can quickly degrade.

---

Common PostgreSQL Constraints

PostgreSQL supports several constraints. The most commonly used ones are:

1. PRIMARY KEY
2. NOT NULL
3. UNIQUE
4. FOREIGN KEY
5. CHECK
6. DEFAULT

---

1. PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each row in a table.

Rules:

• Must be unique
• Cannot be NULL
• Only one primary key per table

Example

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT,

email TEXT

);

Here:

• id uniquely identifies each user

---

2. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have NULL values.

Example

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

email TEXT

);

In this table:

• name must always have a value

---

3. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Example

CREATE TABLE customers (

id SERIAL PRIMARY KEY,

email TEXT UNIQUE

);

This prevents two customers from having the same email address.

---

4. FOREIGN KEY Constraint

The FOREIGN KEY constraint creates a relationship between two tables.

It ensures that a value in one table exists in another table.

---

Example: Parent Table

CREATE TABLE departments (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

---

Example: Child Table

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT,

department_id INTEGER,

CONSTRAINT fk_department

FOREIGN KEY (department_id)

REFERENCES departments(id)

);

This ensures that department_id in employees must exist in departments.

---

5. CHECK Constraint

The CHECK constraint ensures that values meet a specific condition.

Example

CREATE TABLE products (

id SERIAL PRIMARY KEY,

price NUMERIC CHECK (price > 0)

);

This prevents inserting products with negative prices.

---

6. DEFAULT Constraint

The DEFAULT constraint sets a default value if no value is provided.

Example

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

status TEXT DEFAULT 'PENDING'

);

If status is not provided during insert, PostgreSQL stores PENDING.

---

Adding Constraints to an Existing Table

You can add constraints after table creation.

Add NOT NULL

ALTER TABLE users

ALTER COLUMN name SET NOT NULL;

---

Add UNIQUE

ALTER TABLE users

ADD CONSTRAINT unique_email UNIQUE (email);

---

Removing Constraints

To remove a constraint, use DROP CONSTRAINT.

ALTER TABLE users

DROP CONSTRAINT unique_email;

Best Practices for Using Constraints

• Always use a PRIMARY KEY
• Use NOT NULL for mandatory fields
• Use UNIQUE for emails and usernames
• Use FOREIGN KEY to maintain relationships
• Use CHECK for business rules

Constraints make your database strong and reliable.

---

Summary

• Constraints enforce rules on data
• PRIMARY KEY uniquely identifies rows
• NOT NULL prevents empty values
• UNIQUE avoids duplicate data
• FOREIGN KEY maintains relationships
• CHECK enforces conditions
• DEFAULT assigns default values

You now understand PostgreSQL constraints clearly.

---

What’s Next?

Next, we will learn about indexes and how they improve performance.

Next article:
PostgreSQL Indexes Explained with Examples