What is Primary Key in SQL

A Primary Key is a fundamental concept in relational databases used to uniquely identify each record (row) in a table. Think of it as a digital fingerprint or a Social Security number for your data.

Primary Key Characteristics

To qualify as a Primary Key, a column (or set of columns) must follow three strict rules:

• Uniqueness: No two rows can have the same value in the primary key column.
• Non-Null: It cannot contain NULL values. Every record must have a valid identifier.
• Immutability: Once assigned, primary key values should ideally never change, as they are often used to link different tables together.

Types of Primary Keys

• Simple Primary Key: Consists of a single column (e.g., EmployeeID).
• Composite Primary Key: Consists of two or more columns combined to create a unique identifier. This is common in “mapping” tables that link two other entities.
• Surrogate Key: A unique identifier that has no “real world” meaning, often an auto-incrementing integer (e.g., id: 1, 2, 3...).
• Natural Key: A unique identifier that exists in the real world, such as an ISBN number for a book.

Why use a Primary Key?

1. Data Integrity: It ensures that you don’t accidentally insert duplicate records into your database.
2. Fast Retrieval: Databases create a “Clustered Index” on the Primary Key by default, making searches for specific IDs extremely fast.
3. Relationships: It acts as the target for Foreign Keys in other tables, allowing you to create structured relationships (like linking a CustomerID in an Orders table to the main Customers table).

SQL Syntax Example

You can define a primary key when creating a new table or by modifying an existing one.

Creating a table with a Primary Key:

CREATE TABLE Users (
    UserID int NOT NULL,
    UserName varchar(255) NOT NULL,
    Email varchar(255),
    PRIMARY KEY (UserID)
);

Adding a Primary Key to an existing table:

ALTER TABLE Users
ADD PRIMARY KEY (UserID);

Note: A table can have only one primary key. While a primary key can consist of multiple columns (composite), you cannot have two separate primary keys in a single table.