Summary: in this tutorial, you will learn how to use the Db2 UNIQUE constraint to ensure the uniqueness of values stored in a column or a group of columns.
Introduction to Db2 UNIQUE constraint
Db2 UNIQUE constraints ensure that the values in a column are unique and not null for all rows in the table. For example, you may want the email is unique for every member.
To define a unique constraint for a column of a table, you use the following one of the following syntaxes:
1 2 3 4 5 | CREATE TABLE table_name( ..., column_name type UNIQUE, ... ); |
or
1 2 3 4 5 6 | CREATE TABLE table_name( ..., column_name type, ..., CONSTRAINT name UNIQUE(column_name) ); |
Behind the scenes, Db2 creates a unique index to enforce the uniqueness of the value in the column_name.
Let’s take an example of using a unique constraint.
Db2 UNIQUE constraint example
First, create the members table whose email column will contain unique email addresses:
1 2 3 4 5 6 7 | CREATE TABLE members ( member_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL , first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(200) NOT NULL UNIQUE, PRIMARY KEY(member_id) ); |
Second, insert a new row into the members table:
1 2 3 4 | INSERT INTO members(first_name, last_name, email) VALUES ('John','Doe','john.doe@example.com'); |
Third, attempt to insert a new row with an email that already exists in the members table:
1 2 3 4 | INSERT INTO members(first_name, last_name, email) VALUES ('John Joker','Doe','john.doe@example.com'); |
Db2 issued the following error due to unique constraint violation:
1 2 3 4 | SQL0803N 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. |
Creating UNIQUE constraints for a group of columns
To define a unique constraint for a group of columns, you use the following syntax:
1 2 3 4 5 6 7 | CREATE TABLE table_name( ..., column_name_1 type NOT NULL, column_name_2 type NOT NULL, ..., UNIQUE(column_name_1,column_name_2,...) ); |
In this case, Db2 will create a unique index that includes all the columns in the unique constraint to enforce the uniqueness. It will use the combination of values in the columns to evaluate duplicates.
Adding UNIQUE constraints to an existing table
Before adding a unique constraint to a column or a group of columns, first, you must ensure that the current data is unique for all rows in the table.
Then, you can use the following statement to add a unique constraint to a table:
1 2 3 | ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name_1, column_name_2,...); |
Suppose, you want the combination of first name and last name of members to be unique. To achieve this, you use the following statement:
1 2 3 | ALTER TABLE members ADD CONSTRAINT name UNIQUE(first_name,last_name); |
The following statement inserts a new row into the members table:
1 2 3 4 | INSERT INTO members(first_name, last_name, email) VALUES ('Jane','Doe','jane.doe@example.com'); |
The statement works because the combination of the first name and last name is not duplicate:
1 2 3 4 | INSERT INTO members(first_name, last_name, email) VALUES ('Jane','Doe','jane.doe2@example.com'); |
Here is the error message:
1 2 3 4 | SQL0803N 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 "3" constrains table "DB2ADMIN.MEMBERS" from having duplicate values for the index key. |
Removing UNIQUE constraints
The ALTER TABLE DROP CONSTRAINT statement removes the UNIQUE constraint:
1 2 | ALTER TABLE table_name DROP CONSTRAINT constraint_name; |
For example, this statement removes the name constraint from the members table:
1 2 | ALTER TABLE members DROP CONSTRAINT name; |
In this tutorial, you have learned how to use the Db2 UNIQUE constraint to enforce the uniqueness of values in a column or group of columns in all rows of a table.