SQL - Unique Index

SQL Unique Indexes and Constraints

A unique index serves two primary purposes in a database: it enforces data integrity by ensuring that all values in a column (or a combination of columns) are unique, and it improves query performance by allowing the database to quickly find rows based on those unique values. It is a powerful tool for maintaining clean data and optimizing lookups.

Unique Index vs. Unique Constraint

In modern SQL, the concepts of a UNIQUE constraint and a UNIQUE index are closely related. When you declare a UNIQUE constraint on a column, the database system typically creates a unique index behind the scenes to enforce it. The best practice is to use the UNIQUE constraint syntax, as it more clearly declares the intent to enforce a business rule.

• UNIQUE Constraint: A rule defined on a table that prevents duplicate values in one or more columns. It’s a part of the table’s definition.
• UNIQUE Index: The underlying data structure that the database uses to efficiently enforce the uniqueness rule and speed up queries. You can also create a unique index directly.

Syntax

While you can use CREATE UNIQUE INDEX, the more standard and declarative approach is to use a constraint, either during table creation or by altering it later.

-- Method 1: During table creation
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2)
);

-- Method 2: Adding a constraint to an existing table
ALTER TABLE products
ADD CONSTRAINT uq_product_code UNIQUE (product_code);

Example: Enforcing Uniqueness

Let’s use a products table. The product_code must be unique for each product.

INSERT INTO products (product_code, name, price) VALUES ('A-101', 'Super Widget', 19.99);
-- This succeeds.

INSERT INTO products (product_code, name, price) VALUES ('A-102', 'Mega Gadget', 29.99);
-- This also succeeds.

Now, if we try to insert another product with an existing product_code, the database will raise an error, protecting our data integrity.

INSERT INTO products (product_code, name, price) VALUES ('A-101', 'Another Widget', 15.99);

-- Output will be an error:
-- ERROR: duplicate key value violates unique constraint "uq_product_code"
-- DETAIL:  Key (product_code)=(A-101) already exists.

The Role of NULL Values

How unique indexes/constraints handle NULL values can differ between database systems, which is a critical point for cross-platform development.

• SQL Standard (PostgreSQL, Oracle, etc.): The standard behavior is to allow multiple NULL values in a unique column. This is because in SQL, NULL is not considered equal to any other value, including another NULL.
• Non-Standard (SQL Server): SQL Server enforces a stricter rule, allowing only a single NULL value in a column with a unique constraint.

Always consult your specific database’s documentation for its handling of NULL in unique constraints.

Creating a Composite Unique Index

Sometimes, uniqueness is defined by a combination of columns. For example, in a course_enrollments table, a student can enroll in many courses, and a course can have many students, but a student can only enroll in the same course once. The combination of student_id and course_id must be unique.

Example

CREATE TABLE course_enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    -- Composite unique constraint
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

This constraint allows the following inserts:

INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 101); -- OK
INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 102); -- OK
INSERT INTO course_enrollments (student_id, course_id) VALUES (2, 101); -- OK

But it will prevent a duplicate enrollment:

INSERT INTO course_enrollments (student_id, course_id) VALUES (1, 101);
-- ERROR: duplicate key value violates unique constraint "uq_student_course"

Performance Trade-offs

While indexes are essential for performance, they are not free. Understanding the trade-offs is key to good database design.

• Read Performance (Benefit): Indexes dramatically speed up SELECT, UPDATE, and DELETE queries that use the indexed columns in their WHERE clause.
• Write Performance (Cost): INSERT, UPDATE, and DELETE operations become slightly slower because, in addition to modifying the table data, the database must also update the index. For tables with very high write volumes, the number of indexes should be chosen carefully.

Verifying Indexes

You can query your database’s metadata to see which indexes exist on a table. While some databases have simple commands (SHOW INDEX FROM table in MySQL), a more portable method is to query the INFORMATION_SCHEMA.

Standard SQL Approach

SELECT
    indexname,
    indexdef
FROM
    pg_indexes -- This is PostgreSQL specific, but similar views exist elsewhere
WHERE
    tablename = 'products';

For MySQL, you would use:

SHOW INDEX FROM products;

The output will list the indexes on the table, including the primary key index and the unique index/constraint you created.