Understanding SQL Primary Key: A Comprehensive Guide

SQL Primary Key

A column or group of columns that uniquely identifies every entry in a table is known as a Primary Key (PK) in SQL. This key is crucial in ensuring data integrity and optimizing database performance.

Defining a Primary Key in SQL

To create a primary key, you define a PRIMARY KEY constraint when creating or modifying a table. If you use multiple columns as a primary key, it is known as a composite primary key.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Important Considerations for Primary Keys

When designing a composite primary key:

• Use as few columns as possible for better storage and performance.
• More columns in the primary key result in increased storage consumption.
• Less data means faster database processing.

Key Characteristics of a Primary Key

• Ensures entity integrity in the table.
• Always contains unique values.
• Cannot exceed 900 bytes in length.
• Cannot contain NULL values.
• Must contain unique values.
• There can only be one primary key constraint per table.
• Defining a primary key automatically creates a unique index for that column.

Advantages of Using a Primary Key

• Ensures data uniqueness.
• Provides faster data access by creating 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

Here’s how you can create a primary key on the Emp_ID column in 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)

To define a composite primary key on multiple columns, use the following syntax:

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: While this example contains two columns in the primary key (Emp_ID and EmpName), it still counts as one primary key constraint.

Adding a Primary Key Using ALTER TABLE

Use the SQL syntax below to add a primary key constraint to a table that has already been created:

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 using ALTER TABLE to add a primary key, the columns must not contain NULL values.

Dropping a Primary Key Constraint

Use the SQL syntax below to get rid of a primary key constraint from a table:

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
Complete Advance AI topics:- CLICK HERE

Final Thoughts

A Primary Key is fundamental in relational database design. It ensures that every record is unique, improving data reliability and access speed. By following best practices, such as limiting the number of columns in a composite primary key, you can enhance your database’s efficiency and performance. Embrace these strategies to build robust and scalable database systems for your projects.

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