Home / SQL / PRIMARY KEY

PRIMARY KEY

Overview

The PRIMARY KEY constraint uniquely identifies each row in a table. A primary key must be unique and cannot contain NULL values. A table can have only one primary key.

Primary keys are essential for relational databases as they ensure entity integrity and enable relationships between tables through foreign keys.

Basic Syntax

PRIMARY KEY Syntax

-- In CREATE TABLE
CREATE TABLE table_name (
    column_name datatype PRIMARY KEY,
    ...
);

-- Or at table level
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (column1)
);

Examples

Example 1: Single Column Primary Key

Column-level primary key

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

Example 2: Table-Level Primary Key

Table-level primary key

CREATE TABLE Customers (
    customer_id INT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (customer_id)
);

Example 3: Composite Primary Key

Multiple columns as primary key

CREATE TABLE OrderItems (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Example 4: Primary Key with AUTO_INCREMENT

Auto-incrementing primary key

-- MySQL
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- SQL Server
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

Properties of Primary Key

A primary key must satisfy these properties:

Unique: Each value must be unique across all rows
NOT NULL: Cannot contain NULL values

Immutable:

One per table: A table can have only one primary key

Adding Primary Key to Existing Table

You can add a primary key using ALTER TABLE:

Add primary key later

-- First, ensure column is NOT NULL
ALTER TABLE Customers
MODIFY COLUMN customer_id INT NOT NULL;

-- Then add primary key
ALTER TABLE Customers
ADD PRIMARY KEY (customer_id);

Dropping Primary Key

You can remove a primary key constraint:

Remove primary key

ALTER TABLE Customers
DROP PRIMARY KEY;

Primary Key vs Unique

Understanding the difference:

Property	PRIMARY KEY	UNIQUE
NULL values	Not allowed	One NULL allowed (varies)
Count per table	One	Multiple
Index	Creates clustered index	Creates non-clustered index
Purpose	Row identifier	Prevent duplicates

Best Practices

Always use primary key: Every table should have one
Use single column when possible: Simpler than composite keys
Use surrogate keys: Auto-incrementing integers are common
Keep it simple: Avoid complex composite keys when possible
Don't change values: Primary keys should be immutable
Meaningful when appropriate: Natural keys can be used if stable

Common Mistakes

Forgetting primary key: Tables without PKs can cause issues
Using NULL: Primary key columns cannot be NULL
Changing values: Altering PK values can break relationships
Too many columns: Composite keys should be minimal

Next Steps

Learn about linking tables with FOREIGN KEY, or explore other constraints in Constraints.

← Sql Constraints Foreign Key →