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, ... );
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,...) );
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);
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;
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, ..., );
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) );
This statement inserts a new person into the
INSERT INTO persons(ssn,first_name,last_name,gender) VALUES ('123-45-6789','John','Doe','M');
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');
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.
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) );
Each time you insert a new row into the
members table, Db2 automatically generates a sequential number for the
INSERT INTO members(first_name,last_name,gender) VALUES ('Mary','Jane','F'), ('Peter','Crane','M');
Here are the contents of the
SELECT * FROM members;
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) );
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) );
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
ALTER TABLE memberships ADD PRIMARY KEY(membership_id);
In this tutorial, you have learned about Db2 primary key and how to create a new table with a primary key.