Creating Unique Indexes in PostgreSQL: A Practical Guide

Learn how to create and manage unique indexes in PostgreSQL to ensure data integrity and optimize database performance. This tutorial covers the syntax, best practices, and use cases for unique indexes. #PostgreSQL #UniqueIndex #SQL #Database #Index #PostgreSQLTutorial

Creating Unique Indexes in PostgreSQL

Understanding Unique Indexes

In PostgreSQL, a unique index ensures that all values in a column (or a combination of columns) are unique. This is crucial for maintaining data integrity, preventing duplicate entries, and improving database performance. A unique index is often created automatically when you define a primary key constraint on a table. A unique index does not prevent multiple `NULL` values because `NULL` is considered different from any other value.

Creating a Unique Index

You create a unique index using the `CREATE UNIQUE INDEX` command. The index name must be unique within the database. You specify the columns to include in the index within parentheses.

`CREATE UNIQUE INDEX` Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Example: Creating and Using a Unique Index

This example demonstrates creating a unique index in PostgreSQL. First, a table (`Consumer`) is created, and then a unique index is added to the `Mobile_number` column. This prevents inserting multiple rows with the same mobile number. Then, we will add an email column and then create a multi-column index and test its functionality. Note that a primary key is automatically a unique index.

1. Create Table

CREATE TABLE Consumer (
    consumer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    Mobile_number VARCHAR(20) UNIQUE 
);

2. Add Email Column

ALTER TABLE Consumer ADD email VARCHAR(255);

3. Create Single-Column Unique Index

CREATE UNIQUE INDEX idx_consumer_email ON Consumer (email);

4. Testing the Unique Index (Single Column)

Attempting to insert a duplicate email address will result in an error.

5. Create Multi-Column Unique Index

Add two columns and create a multi-column unique index that enforces that the combination of address and work_address columns must be unique.

ALTER TABLE Consumer ADD address VARCHAR(255), ADD work_address VARCHAR(200);
CREATE UNIQUE INDEX idx_consumer_address ON Consumer (address, work_address);

6. Testing the Unique Index (Multi-Column)

Attempting to insert a row with a duplicate combination of address and work_address values will result in an error.

Conclusion

Unique indexes in PostgreSQL are essential for maintaining data integrity and improving database performance. Understanding how to create and use single-column and multi-column unique indexes is key for designing efficient and robust database applications.