Understanding SQL Primary Key: A Comprehensive Guide

SQL Primary Key

A Primary Key (PK) in SQL is a column or combination of columns used to uniquely identify each record in a database table. It plays an important role in maintaining data integrity and can also improve the efficiency of data retrieval.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Defining a Primary Key in SQL

A primary key is created by applying the PRIMARY KEY constraint while creating or modifying a table. When two or more columns are used together to identify records uniquely, the result is called a composite primary key.

Important Considerations for Primary Keys

When creating a composite primary key, it is important to keep the following points in mind:

• Use the minimum number of columns required to maintain better storage and performance.
• Adding more columns to a primary key increases its storage requirements.
• Using less data can help improve database processing speed.

Key Characteristics of a Primary Key

• Maintains entity integrity within a table.
• Contains only unique values.
• Cannot be more than 900 bytes in length.
• Cannot contain NULL values.
• Every value must be unique.
• A table can have only one PRIMARY KEY constraint.
• Creating a primary key automatically creates a unique index for the column.

Advantages of Using a Primary Key

• Helps maintain data uniqueness.
• Provides faster data access through an efficient index.

Note: In Oracle, a primary key cannot contain more than 32 columns.

Creating a Primary Key in SQL

Primary Key for One Column

The following example creates a primary key on the Emp_ID column of the employees table.

MySQL Syntax:

CREATE TABLE employees
(
    Emp_ID int NOT NULL,
    EmpName varchar (255) NOT NULL,
    Department varchar (255),
    Salary int,
    City varchar (255),
    PRIMARY KEY (Emp_ID)
);

SQL Server / Oracle / MS Access Syntax:

CREATE TABLE employees
(
    Emp_ID int NOT NULL PRIMARY KEY,
    EmpName varchar (255) NOT NULL,
    Department varchar (255),
    Salary int,
    City varchar (255)
);

Primary Key for Multiple Columns (Composite Primary Key)

When a single column is not enough to uniquely identify a record, multiple columns can be combined to create a composite primary key.

MySQL / SQL Server / Oracle / MS Access Syntax:

CREATE TABLE employees
(
    Emp_ID int NOT NULL,
    EmpName varchar (255) NOT NULL,
    Department varchar (255),
    Salary int,
    City varchar (255),
    CONSTRAINT pk_Employee PRIMARY KEY (Emp_ID, EmpName)
);

Note: Although this example uses two columns, Emp_ID and EmpName, together as the primary key, they are still considered one primary key constraint.

Adding a Primary Key Using ALTER TABLE

If a table has already been created, you can use ALTER TABLE to add a primary key constraint.

Primary Key on One Column

ALTER TABLE employees
ADD PRIMARY KEY (Emp_ID);

Primary Key on Multiple Columns

ALTER TABLE employees
ADD CONSTRAINT pk_Employee PRIMARY KEY (Emp_ID, EmpName);

Important: When adding a primary key with ALTER TABLE, the selected columns must not contain NULL values.

Dropping a Primary Key Constraint

If you need to remove a primary key constraint from a table, you can use the following commands.

MySQL Syntax

ALTER TABLE employees
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access Syntax

ALTER TABLE employees
DROP CONSTRAINT pk_Employee;

Download New Real Time Projects :- Click here

Final Thoughts

A Primary Key is an essential part of relational database design because it ensures that records can be uniquely identified and helps maintain reliable data. It can also improve data access performance through indexing.

When working with composite primary keys, keeping the number of columns as small as practical can help control storage requirements and improve efficiency. By applying these principles correctly, you can create more reliable and scalable database systems for your projects.

SEO Keywords

sql primary key w3schools
sql foreign key
sql primary key example
sql primary key auto increment
how to add primary key to existing table in sql
unique key in sql
primary key example
joins in sql
sql primary key
sql primary key auto increment
sql primary key index
sql primary key multiple columns
sql primary key and foreign key