{"id":585,"date":"2019-06-04T01:51:07","date_gmt":"2019-06-04T09:51:07","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=585"},"modified":"2020-06-29T23:14:41","modified_gmt":"2020-06-30T07:14:41","slug":"db2-primary-key","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-primary-key\/","title":{"rendered":"Db2 Primary Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 PRIMARY KEY<\/code> constraint to define a primary key for a table.<\/p>\n\n\n\n

Db2 primary key overview<\/h2>\n\n\n\n

A primary key of a table is a column or group of columns whose values uniquely identify every row in the table.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

To define a primary key for a table, you use the PRIMARY KEY<\/code> constraint.<\/p>\n\n\n\n

Creating a table with a primary key<\/h3>\n\n\n\n

The following statement creates a table<\/a> with a primary key:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name (\n\tpk_column type<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>,\n\t...\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the primary key consists of two or more columns, you use the following syntax:<\/p>\n\n\n
CREATE TABLE table_name (\n\tpk_column1 type NOT NULL<\/span>,\n\tpk_column2 type NOT NULL<\/span>,\n\t...,\n\tPRIMARY KEY(pk_column1,pk_column2,...)\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
Adding a primary key to an existing table<\/h3>\n\n\n\n
To add a primary key to a table, you use the following form of ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\n[ADD<\/span> CONSTRAINT<\/span> constraint_name]\nPRIMARY KEY<\/span> (primary_key_columns);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the ADD CONSTRAINT<\/code> clause is optional.<\/p>\n\n\n\n
Removing a primary key from a table<\/h3>\n\n\n\n
To remove a primary key from a table, you use the following form of the ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name \nDROP<\/span> PRIMARY KEY<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Primary key and NOT NULL constraint<\/h3>\n\n\n\n
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<\/code> constraint. However, in DB2, you must explicitly declare all primary key columns as NOT NULL<\/code> columns.<\/p>\n\n\n\n
Primary key and identity column<\/h3>\n\n\n\n
Because the primary key must contain unique values that uniquely identify each row of a table, the identity column<\/a> is an ideal choice for the primary key.<\/p>\n\n\n\n
The following syntax shows how to create a table with a primary key as an identity column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name(\n\tpk_column INT<\/span> \n\t\tGENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> \n\t\tNOT<\/span> NULL<\/span> \n\t\tPRIMARY KEY<\/span>,\n\t...,\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 primary key examples<\/h2>\n\n\n\n
Let’s take some examples of creating new tables with primary keys.<\/p>\n\n\n\n
1) Creating a table with a primary key example<\/h3>\n\n\n\n
Suppose SSN<\/a> uniquely identifies a person. The following statement creates the persons<\/code> table whose primary key is the SSN column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> persons (\n\tssn VARCHAR<\/span>(11<\/span>) NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>,\n\tfirst_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tlast_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tgender CHAR<\/span>(1<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> \n\tpersons(ssn,first_name,last_name,gender)\nVALUES<\/span>\n\t('123-45-6789'<\/span>,'John'<\/span>,'Doe'<\/span>,'M'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> \n\tpersons(ssn,first_name,last_name,gender)\nVALUES<\/span>\n\t('123-45-6789'<\/span>,'Jane'<\/span>,'Doe'<\/span>,'F'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 issued the following error:<\/p>\n\n\n
SQL0803N  One or more values in<\/span> the INSERT statement, UPDATE statement, or\nforeign key update caused by a DELETE statement are not valid because the\nprimary key, unique constraint or unique index identified by \"1\"<\/span> constrains\ntable \"DB2ADMIN.PERSONS\"<\/span> from<\/span> having duplicate values for<\/span> the index key.<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Creating a table with a primary key as an identity column example<\/h3>\n\n\n\n
The following example creates a table named members<\/code> with a primary key column as an identity column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> members(\n\tmember_id INT<\/span> \n\t\tGENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> \n\t\tPRIMARY KEY<\/span>,\n\tfirst_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tlast_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tgender CHAR<\/span>(1<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Each time you insert a new row into the members<\/code> table, Db2 automatically generates a sequential number for the member_id<\/code> column.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n\tmembers(first_name,last_name,gender)\nVALUES<\/span>\n\t('Mary'<\/span>,'Jane'<\/span>,'F'<\/span>),\n\t('Peter'<\/span>,'Crane'<\/span>,'M'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here are the contents of the members<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> members;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
Notice that the member_id<\/code> has the sequential integer 1 and 2.<\/p>\n\n\n\n
3) Creating a new table whose primary key has more than one column example<\/h3>\n\n\n\n
The following example creates a new table whose primary key contains two columns:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> enrollments (\n\tmember_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tmembership_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tstart_date DATE<\/span> NOT<\/span> NULL<\/span>,\n\tPRIMARY KEY<\/span>(member_id, membership_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Adding a primary key to an existing table example<\/h3>\n\n\n\n
This example creates a new table without a primary key:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> memberships(\n\tmembership_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tname<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tdescription VARCHAR<\/span>(150<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Before promoting the membership_id<\/code> 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<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> memberships\n\tADD<\/span> PRIMARY KEY<\/span>(membership_id);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned about Db2 primary key and how to create a new table with a primary key.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The following statement attempts to insert a new row<\/a> into the persons<\/code> table with an SSN that already exists:<\/p>\n\n\n
This statement inserts<\/a> a new person into the persons<\/code> table:<\/p>\n\n\n