SQL — Primary Key

What is a Primary Key?

A primary key uniquely identifies each row in a table. It cannot contain NULL values and must be unique across all rows.

Basic Syntax

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

AUTO_INCREMENT Primary Key

The most common pattern — let the database generate IDs:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

Composite Primary Key

Use multiple columns as the key:

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

The combination of order_id and product_id must be unique — but each can repeat individually.

Adding Primary Key to Existing Table

ALTER TABLE users ADD PRIMARY KEY (id);

Naming a Primary Key

CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  CONSTRAINT pk_products PRIMARY KEY (id)
);

Dropping a Primary Key

-- MySQL
ALTER TABLE users DROP PRIMARY KEY;

-- PostgreSQL
ALTER TABLE users DROP CONSTRAINT pk_users;

Primary Key vs Unique

Feature	PRIMARY KEY	UNIQUE
NULL values	Not allowed	Usually allowed (one NULL)
Per table	Only one	Multiple
Purpose	Row identifier	Prevent duplicates

Surrogate vs Natural Keys

Surrogate Key (recommended)

A meaningless auto-generated ID:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL
);

Natural Key

Uses real data as the key:

CREATE TABLE users (
  email VARCHAR(100) PRIMARY KEY  -- natural but risky
);

Natural keys can change (people change emails), making them fragile.

Practical Example

CREATE TABLE courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  instructor_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (instructor_id) REFERENCES instructors(id)
);

Best Practices

• Always use a surrogate key (id INT AUTO_INCREMENT PRIMARY KEY) as the primary key
• Keep primary keys simple — integers are faster than strings
• Never modify a primary key value (use separate updates)
• Always add a primary key — tables without one can't be efficiently joined or updated
• Use composite primary keys only for junction/bridge tables

Practice

1. Create a students table with an auto-incrementing primary key
2. Create a grades table with a composite primary key (student_id, course_id)
3. Add a primary key to an existing table using ALTER TABLE
4. Explain the difference between surrogate and natural keys