What is a Primary Key?

A primary key is a unique identifier for each row in a table. It ensures that each row is unique and not null, maintaining data integrity and helping with fast data retrieval. A primary key is typically defined on one or more columns of a table. The database uses the primary key to identify each row in the table, making it important for organizing and retrieving data more efficiently.

It:

• Ensures Uniqueness: No two rows in a table can have the same primary key value.
• Prevents Null Values or duplicates: A primary key column cannot have null or duplicat values.
• Optimizes Access: Enables faster access to rows when performing queries.

Declaring a Primary Key

We can define a primary key when we create a table by using the PRIMARY KEY constraint on one or more columns. This constraint specifies that the column(s) must contain unique, non-null values, and DB2 will automatically applies these rules.

Syntax -

CREATE TABLE table_name (
column1 data_type [NOT NULL],
column2 data_type [NOT NULL],
...
PRIMARY KEY (column1, column2, ...)
IN database_name.tablespace_name;

• table_name: The name of the table.
• column: Column(s) that make up the primary key. Each column must be defined as NOT NULL.
• PRIMARY KEY (column_list): Specifies which columns constitute the primary key for the table.

Examples - Defining a Primary Key on a Table

CREATE TABLE EMPLOYEE (
EMP_ID INTEGER NOT NULL,
EMP_NAME CHAR(50),
DEPARTMENT CHAR(30),
SALARY DECIMAL(10, 2),
JOIN_DATE DATE,
PRIMARY KEY (EMP_ID)
)
IN EMPLDB.EMPLTS;

• EMPLOYEE: Creates a table named EMPLOYEE.
• EMP_ID: Defines EMP_ID as the primary key, ensuring unique, non-null values in this column.
• PRIMARY KEY (EMP_ID): Sets EMP_ID as the primary key, which will uniquely identify each employee record.

Altering a Primary Key

DB2 does not allow direct modification of primary key constraints. If we need to alter the primary key, we should:

1. Drop the existing primary key.
2. Add a new primary key constraint.

Dropping the Primary Key -

To remove an existing primary key, use the ALTER TABLE statement with DROP PRIMARY KEY.

ALTER TABLE table_name
DROP PRIMARY KEY;

Adding a New Primary Key -

After dropping the primary key, you can add a new one using ALTER TABLE with ADD PRIMARY KEY.

ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

Examples - Altering a Primary Key on a Table

Suppose we need to change the primary key on the EMPLOYEE table from EMP_ID to a composite key of EMP_ID and DEPARTMENT.

Dropping the Primary Key -

ALTER TABLE EMPLOYEE
DROP PRIMARY KEY;

Adding a New Primary Key -

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID, DEPARTMENT);

• DROP PRIMARY KEY: Removes the existing primary key constraint on EMP_ID.
• ADD PRIMARY KEY: Creates a composite primary key using EMP_ID and DEPARTMENT.

Deleting a Primary Key

To delete a primary key constraint from a table, use the ALTER TABLE statement with DROP PRIMARY KEY. This will remove the primary key constraint but will not delete any data from the table.

Syntax -

ALTER TABLE table_name
DROP PRIMARY KEY;

Examples - Deleting a Primary Key

ALTER TABLE EMPLOYEE
DROP PRIMARY KEY;

---