DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 UNIQUE Constraint

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, ... );

or

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:

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:

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:

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:

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:

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:

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:

ALTER TABLE members ADD CONSTRAINT name UNIQUE(first_name,last_name);

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

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:

INSERT INTO members(first_name, last_name, email) VALUES ('Jane','Doe','jane.doe2@example.com');

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.

Removing UNIQUE constraints

The ALTER TABLE DROP CONSTRAINT statement removes the UNIQUE constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

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

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 NOT NULL
Next Db2 CHECK Constraint

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.