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)

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.