SQL PRIMARY KEY

In the world of relational databases, data integrity is everything. The PRIMARY KEY constraint is the most fundamental tool you have to ensure that every row in your table is unique and reachable. Think of a primary key as a "unique fingerprint" or a digital ID card for a specific record. Without it, finding or updating a specific row becomes difficult and prone to errors.

A primary key ensures that no two rows are identical and that every row has a valid identifier. This is critical when you need to link tables together using foreign keys.

Key Features of SQL PRIMARY KEY

• Uniqueness: Every value in the primary key column must be different. No two rows can share the same ID.
• No NULLs: A primary key column cannot contain NULL values. Every record must have a valid key.
• Implicit Indexing: When you define a primary key, the database automatically creates a "Clustered Index." This makes searching for specific records incredibly fast.
• Single PK per Table: A table can only have one primary key constraint. However, that single key can consist of one or more columns.
• Immutability: While technically possible to change a primary key value, it is a best practice to choose values that will never need to change.

SQL PRIMARY KEY Syntax

Single Column Primary Key

This is the most common approach. You define one column (usually an ID) to serve as the unique identifier for the entire row.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

• The EmployeeID column is marked as the primary key. If you try to insert two employees with ID #1, the database will throw an error and block the second entry.

Composite Primary Key (Multiple Columns)

Sometimes, a single column isn't enough to guarantee uniqueness. In these cases, you can combine two or more columns to form a Composite Key. This is very common in "mapping" tables that link two other tables together.

CREATE TABLE ProjectAssignments (
    EmployeeID INT,
    ProjectID INT,
    Role VARCHAR(50),
    PRIMARY KEY (EmployeeID, ProjectID)
);

• In this example, an employee can work on many projects, and a project can have many employees. The combination of EmployeeID and ProjectID ensures that an employee isn't assigned to the exact same project more than once.

Adding a PRIMARY KEY to an Existing Table

Adding a Primary Key After Table Creation

If you've already created a table and realized you forgot the primary key, or if you are refactoring an old database, you can use the ALTER TABLE command. However, there's a catch: the column you choose must already be free of NULL values and duplicates.

ALTER TABLE Employees  
ADD PRIMARY KEY (EmployeeID);

Removing a PRIMARY KEY

There are rare occasions where you might need to remove a primary key for example, if you are changing the table structure significantly or replacing a simple key with a composite key.

/* Syntax for MySQL */
ALTER TABLE Employees  
DROP PRIMARY KEY;

/* Syntax for SQL Server / Oracle / PostgreSQL */
ALTER TABLE Employees
DROP CONSTRAINT PK_Employee_ID;

Summary

• A PRIMARY KEY acts as the unique identifier for every row in your database table.
• It enforces Data Integrity by preventing duplicates and forbidding NULL values.
• Defining a primary key automatically creates an index, which significantly boosts query performance.
• While you can only have one primary key per table, it can be a Composite Key involving multiple columns if the business logic requires it.
• You can manage primary keys using CREATE TABLE for new projects or ALTER TABLE for existing ones.