Primary Key in DBMS

A primary key is a column or a set of columns that uniquely identifies each row (record) in a database table.

• A primary key can consist of one or more attributes (columns).
• The values of a primary key must be unique for every row and cannot be NULL.
• A table can have only one primary key, although the primary key may contain multiple columns (composite primary key).

Syntax For Creating and Deleting Primary Key

A primary key can be defined while creating a table or added to an existing table using ALTER TABLE.

Syntax

While Creating a Table:

CREATE TABLE table_name (

column_name data_type PRIMARY KEY

);

For an Existing Table:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

For Deleting a Primary Key:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example of Primary Key

Consider an EMPLOYEE table containing employee details such as EMP_ID, Name, Salary, and Department. Since each employee has a unique EMP_ID, it can be defined as the primary key of the table.

CREATE TABLE EMPLOYEE (

EMP_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary INT,

Department VARCHAR(50)

);

Here, EMP_ID uniquely identifies each employee and cannot contain duplicate or NULL values.

Rules For Defining a Primary Key

A table may have multiple candidate keys, but only one candidate key is selected as the primary key.

• Minimal: A primary key should contain the minimum number of attributes required to uniquely identify each record.
• Non-NULL: Primary key attributes cannot contain NULL values.
• Unique: No two rows can have the same primary key value.
• Stable: The value of a primary key should remain unchanged throughout the record's lifetime.
• Accessible: The primary key should be easy to use for identifying and accessing records.

Advantages

• Uniqueness: Ensures that each record in a table can be uniquely identified, preventing duplicate key values.
• Data Integrity: Maintains the accuracy and consistency of data by providing a unique identifier for each record.
• Prevents NULL Values: Primary key attributes cannot contain NULL values, ensuring that every record has a valid identifier.
• Efficient Data Retrieval: A primary key can be indexed by the DBMS, allowing faster searching and retrieval of records.
• Establishes Relationships: Primary keys can be referenced by foreign keys in other tables, helping establish relationships between tables.

Primary Key vs Unique Key

Both Primary Key and Unique Key ensure uniqueness, but they differ in their purpose and constraints.