Db2 UNIQUE Index

Summary: in this tutorial, you will learn about Db2 unique indexes and how to use unique indexes to maintain data integrity by ensuring that no two rows in a table have duplicate key values.

Introduction to Db2 unique indexes

Unique indexes are indexes that allow you to maintain data integrity by ensuring that the index key columns do not contain any duplicate data.

You can define a unique index on one or multiple columns of a table. If you include one column in the unique index, Db2 will enforce the uniqueness of values in this column. In case you define a unique index that includes multiple columns, Db2 will enforce the uniqueness of values in these columns.

Any attempt to insert or update data into the unique indexed columns that cause the duplicate will result in an error.

When you create a unique index for the table that already has existing data, Db2 will check the data in the indexed key columns for the uniqueness. If the table contains duplicate key values, the index creation process will fail.

Besides enforcing the uniqueness of data, unique indexes also help improve the speed of the queries that involve indexed key columns.

To create a unique index, you use the CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX index_name
ON table_name(c1,c2, ...);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the unique index in the CREATE UNIQUE INDEX clause.
• Second, specify the name of the table to which the unique index belongs and a list of columns that will be included in the index.

Db2 unique index examples

Let’s take some examples of using Db2 unique indexes.

1) Creating a Db2 unique index on one column example

First, create a table that stores member data:

CREATE TABLE members(
    member_id INT NOT NULL 
        GENERATED ALWAYS AS IDENTITY 
        PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    joined_on DATE NOT NULL DEFAULT CURRENT DATE
);
Code language: SQL (Structured Query Language) (sql)

Next, create a unique index that includes the email column to ensure the email is unique for every member.

CREATE UNIQUE INDEX ix_unq_email
ON members(email);
Code language: SQL (Structured Query Language) (sql)

Then, insert a new member into the members table:

INSERT INTO members(first_name, last_name, email)
VALUES('john','doe','[email protected]');
Code language: SQL (Structured Query Language) (sql)

After that, view the member data by using the following SELECT statement:

SELECT * FROM members;
Code language: SQL (Structured Query Language) (sql)

Finally, attempt to insert a new member with the email that already exists:

INSERT INTO members(first_name, last_name, email)
VALUES('jane','doe','[email protected]');
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error:

SQL Error [23505]: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "2" constrains table "DB2ADMIN.MEMBERS" from having duplicate values for the index key.. 
Code language: SQL (Structured Query Language) (sql)

2) Creating a Db2 unique index on multiple columns example

First, create a book_ratings table that stores book rating data:

CREATE TABLE book_ratings(
    rating_id INT NOT NULL 
        GENERATED ALWAYS AS IDENTITY 
        PRIMARY KEY, 
    rating SMALLINT NOT NULL,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    CHECK(rating >= 1 AND rating <= 5),
    FOREIGN KEY(book_ID) 
        REFERENCES books(book_id)
);
Code language: SQL (Structured Query Language) (sql)

Suppose, each user can rate a book one time only. To enforce this rule, you can create a unique index on the book_id and user_id columns.

Second, create a unique index on the book_id and user_id columns:

CREATE UNIQUE INDEX rating_ip
ON book_ratings(book_id, user_id);
Code language: SQL (Structured Query Language) (sql)

Third, insert a rating into the book_ratings table:

INSERT INTO 
    book_ratings(rating, book_id, user_id)
VALUES
    (4, 1, 20);
Code language: SQL (Structured Query Language) (sql)

In this example, the user_id 20 rated the book id 1 with 4 out of 5.

Fourth, user id 20 attempts to vote the book id 1 one more time:

INSERT INTO book_ratings(rating, book_id, ip_address)
VALUES(5, 1, 20);
Code language: SQL (Structured Query Language) (sql)

Db2 issued an error.

Db2 unique index & NULL treatments

Typically, NULL is not equal to itself. However, unique indexes treat NULL values equally. If a unique index has one column, this column can have only one NULL value.

In case the unique index has multiple columns, these columns will have only one row that has NULL in every column.

Unique index vs. unique constraint

Both unique index and unique constraint are used to enforce the uniqueness of data in one or multiple columns.

When you create a unique constraint, Db2 automatically creates a unique index behind the scene and uses this index to enforce the uniqueness. Therefore, we can say that the unique constraints make the purposes of the unique indexes much more clear.

In this tutorial, you have learned about the Db2 unique index and how to create a unique index for one or many columns of a table.