PostgreSQL Constraints Explained: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL

-

 

PostgreSQL constraints enforce rules on table data. They help maintain data accuracy, prevent invalid records, and preserve relationships between tables.

What Are Constraints?

A constraint is a rule applied to a table or column. PostgreSQL checks the rule when data is inserted or updated.

1. NOT NULL Constraint

NOT NULL prevents a column from storing NULL values.

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

The employee name must be provided for every inserted row.

2. PRIMARY KEY Constraint

A primary key uniquely identifies each row. It cannot contain duplicate or NULL values.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Each department must have a unique department_id.

3. UNIQUE Constraint

A UNIQUE constraint prevents duplicate values in a column or combination of columns.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

This prevents duplicate non-NULL email values. By default, PostgreSQL allows multiple NULL values in a unique column.

4. FOREIGN KEY Constraint

A foreign key enforces a relationship between tables.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT REFERENCES departments(department_id)
);

The referenced department must exist in the departments table, unless department_id is NULL.

To require every employee to have a department, define the column as NOT NULL as well.

5. CHECK Constraint

A CHECK constraint ensures that a condition is satisfied by each inserted or updated row.

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

This prevents negative prices.

A CHECK condition passes when it evaluates to TRUE or NULL, so use NOT NULL separately if the value must be present.

6. DEFAULT Constraint

A default value is used when an insert statement omits a column or explicitly requests DEFAULT.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(30) DEFAULT 'Pending'
);

Example:

INSERT INTO orders (order_id)
VALUES (101);

The new order receives the status Pending.

A default does not prevent someone from explicitly inserting NULL unless the column also has a NOT NULL constraint.

7. Add a Constraint to an Existing Table

You can add constraints after creating a table.

ALTER TABLE employees
ADD CONSTRAINT chk_employee_id_positive
CHECK (employee_id > 0);

PostgreSQL checks existing rows when adding a constraint, unless the chosen method specifically avoids immediate validation. The operation can fail if existing data violates the rule.

8. Remove a Constraint

Use ALTER TABLE ... DROP CONSTRAINT to remove a named constraint.

ALTER TABLE employees
DROP CONSTRAINT chk_employee_id_positive;

Only remove constraints when you understand the effect on data integrity.

Constraint Comparison

Constraint Purpose
NOT NULL Requires a value to be present
PRIMARY KEY Uniquely identifies each row
UNIQUE Prevents duplicate values
FOREIGN KEY Enforces a relationship between tables
CHECK Enforces a condition
DEFAULT Supplies a default value when applicable

Common Mistakes to Avoid

  1. Assuming a foreign key automatically makes the column NOT NULL.
  2. Assuming a default value prevents explicit NULL values.
  3. Forgetting that existing rows must satisfy a newly added constraint.
  4. Treating UNIQUE and PRIMARY KEY as identical.
  5. Using a CHECK constraint without considering NULL values.

Practice Questions

  1. Create a table with a primary key.
  2. Make an employee name mandatory.
  3. Add a unique constraint to an email column.
  4. Create a foreign key between employees and departments.
  5. Prevent negative product prices.
  6. Set a default status for new orders.
  7. Add and remove a named CHECK constraint.

Interview Questions

1. What is a primary key?

A primary key uniquely identifies every row and cannot contain NULL values.

2. What is the difference between PRIMARY KEY and UNIQUE?

A table can have only one primary key, although it may contain multiple columns. A table can have multiple unique constraints. A primary key also requires all its columns to be non-NULL.

3. What is a foreign key?

A foreign key enforces a relationship with a referenced primary key or unique key.

4. What is the purpose of a CHECK constraint?

It prevents rows from being stored when a specified condition evaluates to FALSE.

5. Can a column have both DEFAULT and NOT NULL?

Yes. A default supplies a value when applicable, while NOT NULL prevents NULL values from being stored.

Conclusion

PostgreSQL constraints help maintain accurate and consistent data. Understanding primary keys, foreign keys, unique constraints, CHECK conditions, and defaults is essential for database design and SQL interviews.

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

-
Introduction PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects. In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries. This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers. What You Will Learn By the end of this tutorial, you will understand: What PostgreSQL is and how it works. How to create a database table. How to insert and retrieve records. How to filter and sort data. How to summarize data using aggregate functions. How to practice SQL using a business-related example. What Is PostgreSQL? PostgreSQL is an open-source relational datab...
Read more

PostgreSQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL – Practical SQL Guide

-
Constraints are rules applied to table columns to maintain data accuracy and consistency in a PostgreSQL database. They prevent invalid data from being inserted or updated and help maintain relationships between tables. In this tutorial, you will learn the most commonly used PostgreSQL constraints with practical SQL examples. What Are Constraints in PostgreSQL? Constraints define rules that table data must follow. For example: - An employee ID should be unique. - An employee name should not be NULL. - A salary should not be negative. - An order should reference an existing customer. PostgreSQL provides several constraints to enforce these rules. 1. NOT NULL Constraint The NOT NULL constraint ensures that a column cannot contain NULL values. Suppose you have an employees table. CREATE TABLE employees (     employee_id INT,     employee_name VARCHAR(100) NOT NULL,     department VARCHAR(50) ); In this example, employee_name cannot contain NULL values. Insert ...
Read more