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.
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, ...);
In this syntax:
- First, specify the name of the unique index in the
CREATE UNIQUE INDEXclause.
- 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 );
Next, create a unique index that includes the
CREATE UNIQUE INDEX ix_unq_email ON members(email);
Then, insert a new member into the
INSERT INTO members(first_name, last_name, email) VALUES('john','doe','firstname.lastname@example.org');
After that, view the member data by using the following
SELECT * FROM members;
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@example.com');
Db2 issued the following error:
SQL Error : 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..
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) );
Suppose, each user can rate a book one time only. To enforce this rule, you can create a unique index on the
Second, create a unique index on the
CREATE UNIQUE INDEX rating_ip ON book_ratings(book_id, user_id);
Third, insert a rating into the
INSERT INTO book_ratings(rating, book_id, user_id) VALUES (4, 1, 20);
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);
Db2 issued an error.
Db2 unique index &
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.