Db2 UNIQUE Constraint

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:

CREATE TABLE table_name(
	...,
	column_name type UNIQUE,
	...
);
Code language: SQL (Structured Query Language) (sql)

or

CREATE TABLE table_name(
	...,
	column_name type,
	...,
	CONSTRAINT name UNIQUE(column_name)
);Code language: SQL (Structured Query Language) (sql)

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:

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)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the members table:

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

Third, attempt to insert a new row with an email that already exists in the members table:

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

Db2 issued the following error due to unique constraint violation:

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.
Code language: SQL (Structured Query Language) (sql)

Creating UNIQUE constraints for a group of columns

To define a unique constraint for a group of columns, you use the following syntax:

CREATE TABLE table_name(
	...,
	column_name_1 type NOT NULL,
	column_name_2 type NOT NULL,
	...,
	UNIQUE(column_name_1,column_name_2,...)
);
Code language: SQL (Structured Query Language) (sql)

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:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column_name_1, column_name_2,...);
Code language: SQL (Structured Query Language) (sql)

Suppose, you want the combination of first name and last name of members to be unique. To achieve this, you use the following statement:

ALTER TABLE members
ADD CONSTRAINT name
UNIQUE(first_name,last_name);
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the members table:

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

The statement works because the combination of the first name and last name is not duplicate:

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

Here is the error message:

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.		Code language: JavaScript (javascript)

Removing UNIQUE constraints

The ALTER TABLE DROP CONSTRAINT statement removes the UNIQUE constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Code language: SQL (Structured Query Language) (sql)

For example, this statement removes the name constraint from the members table:

ALTER TABLE members
DROP CONSTRAINT name;
Code language: SQL (Structured Query Language) (sql)

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.