Db2 Primary Key

Summary: in this tutorial, you will learn how to use the Db2 PRIMARY KEY constraint to define a primary key for a table.

Db2 primary key overview

A primary key of a table is a column or group of columns whose values uniquely identify every row in the table.

Each table has one and only one primary key. A primary key is optional. However, it’s a good practice to have a primary key in every table.

To define a primary key for a table, you use the PRIMARY KEY constraint.

Creating a table with a primary key

The following statement creates a table with a primary key:

CREATE TABLE table_name ( pk_column type NOT NULL PRIMARY KEY, ... );
Code language: SQL (Structured Query Language) (sql)

If the primary key consists of two or more columns, you use the following syntax:

CREATE TABLE table_name ( pk_column1 type NOT NULL, pk_column2 type NOT NULL, ..., PRIMARY KEY(pk_column1,pk_column2,...) );
Code language: PHP (php)

When you create a table with the primary key constraint, Db2 automatically creates a unique index on the primary key columns. This unique index is also known as the primary index.

Adding a primary key to an existing table

To add a primary key to a table, you use the following form of ALTER TABLE statement:

ALTER TABLE table_name [ADD CONSTRAINT constraint_name] PRIMARY KEY (primary_key_columns);
Code language: SQL (Structured Query Language) (sql)

Note that the ADD CONSTRAINT clause is optional.

Removing a primary key from a table

To remove a primary key from a table, you use the following form of the ALTER TABLE statement:

ALTER TABLE table_name DROP PRIMARY KEY;
Code language: SQL (Structured Query Language) (sql)

Primary key and NOT NULL constraint

The primary key of a table cannot contain NULL values. In other database systems such as SQL Server, Oracle, and MySQL, the columns included in the primary key columns will implicitly receive the NOT NULL constraint. However, in DB2, you must explicitly declare all primary key columns as NOT NULL columns.

Primary key and identity column

Because the primary key must contain unique values that uniquely identify each row of a table, the identity column is an ideal choice for the primary key.

The following syntax shows how to create a table with a primary key as an identity column:

CREATE TABLE table_name( pk_column INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, ..., );
Code language: SQL (Structured Query Language) (sql)

Db2 primary key examples

Let’s take some examples of creating new tables with primary keys.

1) Creating a table with a primary key example

Suppose SSN uniquely identifies a person. The following statement creates the persons table whose primary key is the SSN column:

CREATE TABLE persons ( ssn VARCHAR(11) NOT NULL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, gender CHAR(1) );
Code language: SQL (Structured Query Language) (sql)

This statement inserts a new person into the persons table:

INSERT INTO persons(ssn,first_name,last_name,gender) VALUES ('123-45-6789','John','Doe','M');
Code language: SQL (Structured Query Language) (sql)

The following statement attempts to insert a new row into the persons table with an SSN that already exists:

INSERT INTO persons(ssn,first_name,last_name,gender) VALUES ('123-45-6789','Jane','Doe','F');
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error:

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 "1" constrains table "DB2ADMIN.PERSONS" from having duplicate values for the index key.
Code language: JavaScript (javascript)

2) Creating a table with a primary key as an identity column example

The following example creates a table named members with a primary key column as an identity column:

CREATE TABLE members( member_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, gender CHAR(1) );
Code language: SQL (Structured Query Language) (sql)

Each time you insert a new row into the members table, Db2 automatically generates a sequential number for the member_id column.

INSERT INTO members(first_name,last_name,gender) VALUES ('Mary','Jane','F'), ('Peter','Crane','M');
Code language: SQL (Structured Query Language) (sql)

Here are the contents of the members table:

SELECT * FROM members;
Code language: SQL (Structured Query Language) (sql)
Db2 Primary Key as Identity column

Notice that the member_id has the sequential integer 1 and 2.

3) Creating a new table whose primary key has more than one column example

The following example creates a new table whose primary key contains two columns:

CREATE TABLE enrollments ( member_id INT NOT NULL, membership_id INT NOT NULL, start_date DATE NOT NULL, PRIMARY KEY(member_id, membership_id) );
Code language: SQL (Structured Query Language) (sql)

4) Adding a primary key to an existing table example

This example creates a new table without a primary key:

CREATE TABLE memberships( membership_id INT NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(150) );
Code language: SQL (Structured Query Language) (sql)

Before promoting the membership_id as the primary key, you first need to make sure that this column does not have duplicate values. Then, you use the following statement to add a primary key to the memberships table:

ALTER TABLE memberships ADD PRIMARY KEY(membership_id);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about Db2 primary key and how to create a new table with a primary key.

Was this tutorial helpful ?