SQL Primary Key

A PRIMARY KEY constraint uniquely identifies each record in a database table. A primary key must contain unique values and cannot contain NULL values.

A table can have only one primary key, which may consist of a single column or multiple columns (known as a composite key).

Primary Key Rules & Characteristics

1. Uniqueness: No two rows can share the same primary key value.
2. Non-Nullability: Primary key columns implicitly enforce NOT NULL.
3. Single Constraint per Table: A table can have only one primary key constraint, though it can span multiple columns.
4. Clustered Index Creation: By default, most database engines (like SQL Server and MySQL InnoDB) automatically create a clustered index on the primary key column to optimize data retrieval speeds.

Syntax & Examples

1. Single-Column Primary Key at Creation

You can define a primary key directly inline on a single column:

-- Column-Level Definition
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    Email      VARCHAR(100) NOT NULL
);

2. Composite Primary Key (Multiple Columns)

When a single column isn't enough to uniquely identify a record, you can combine multiple columns into a composite primary key. Composite primary keys must be defined at the table level:

CREATE TABLE OrderItems (
    OrderID    INT NOT NULL,
    ProductID  INT NOT NULL,
    Quantity   INT NOT NULL,
    UnitPrice  DECIMAL(10, 2) NOT NULL,
    -- Composite Primary Key on OrderID + ProductID
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderID, ProductID)
);

In this table, individual OrderID or ProductID values can repeat, but the combination of (OrderID, ProductID) must be unique across all rows.

3. Adding a Primary Key to an Existing Table

If a table was created without a primary key, you can add one later using ALTER TABLE. The column(s) must already be defined as NOT NULL.

-- Step 1: Ensure column is NOT NULL
ALTER TABLE Customers
ALTER COLUMN CustomerID INT NOT NULL;

-- Step 2: Add Primary Key Constraint
ALTER TABLE Customers
ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

4. Auto-Generating Primary Key Values

Manually providing unique integers for primary keys can lead to collision errors. Database engines offer auto-incrementing surrogate key strategies to handle this automatically:

-- PostgreSQL (ANSI Standard Identity)
CREATE TABLE Customers (
    CustomerID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

-- MySQL / MariaDB
CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

-- SQL Server (T-SQL)
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

Dropping a Primary Key

To remove a primary key constraint from an existing table:

-- SQL Server / PostgreSQL / Oracle
ALTER TABLE Customers
DROP CONSTRAINT PK_Customers;

-- MySQL (No constraint name needed since only one PK exists per table)
ALTER TABLE Customers
DROP PRIMARY KEY;

Note: You cannot drop a primary key if its values are currently referenced by active FOREIGN KEY constraints in another table. You must drop the foreign key reference first.

Natural Key vs. Surrogate Key