PRIMARY KEY

A PRIMARY KEY is the definitive identifier for every row in a table.

It ensures no duplicate values exist and that the identifying column never stores NULL. Because of these guarantees, primary keys become the foundation for relationships, indexes, and fast lookups.

What a Primary Key Does

A primary key enforces two rules at once:

Every value is unique
No value is NULL

Example:

sql exampleWoHoTech

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

StudentID now uniquely identifies every student.

Defining a PRIMARY KEY

You can define the primary key inline:

sql exampleWoHoTech

StudentID INT PRIMARY KEY

Or as a table constraint:

sql exampleWoHoTech

CREATE TABLE Students (
    StudentID INT,
    Name VARCHAR(100),
    PRIMARY KEY (StudentID)
);

The table-level form is required when using composite primary keys.

Composite Primary Key

A single column does not always provide enough uniqueness. A composite primary key uses two or more columns together.

sql exampleWoHoTech

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

The pair (StudentID, CourseID) must be unique, but StudentID can appear in other courses.

Real-World Uses

Primary keys appear in nearly every table:

Table	Typical Primary Key
Users	UserID
Orders	OrderID
Products	ProductID
Employees	EmployeeID

Common naming conventions are id, user_id, order_id, or employee_id.

Pseudocode example:

If User exists: For each row in Users: Print UserID and Name Query orders: Join Orders ON Orders.UserID = Users.UserID

Primary Key vs UNIQUE

Property	PRIMARY KEY	UNIQUE
Allows NULL?	No	Yes (limited)
How many per table?	One	Multiple
Creates index?	Yes	Yes
Identifies row?	Yes	No

Use a primary key for row identity. Use UNIQUE when you only need to prevent duplicates.

Altering Primary Keys

sql exampleWoHoTech

ALTER TABLE Students
ADD PRIMARY KEY (StudentID);

sql exampleWoHoTech

ALTER TABLE Students
DROP PRIMARY KEY;

Common Mistakes

Choosing a volatile column as a primary key Use a key that will never change. A student's StudentID is stable; their email or phone number might change.

Using natural keys when a surrogate is better A composite of first name and last name is not a good primary key because names can collide or change. An auto-incrementing id is safer.

Frequently Asked Questions

Q: Can a table exist without a primary key? A: Yes, but it is not recommended. Without a primary key, database engines struggle to reliably identify rows, replicate data, or build foreign key relationships.

Q: Does a primary key always have to be an integer? A: No. Strings, UUIDs, and composite keys also work. Integer auto-increment keys are the most common because they are small, fast, and simple.

Q: Can I update a primary key value? A: Yes, but avoid it. Changing a primary key breaks dependent foreign keys unless the change cascades.

Summary

A primary key uniquely identifies each row.
It automatically enforces NULL and duplicate restrictions.
Use it as the anchor for relationships with other tables.
Choose values that are stable and permanent.

Next Step

See FOREIGN KEY to learn how tables relate to each other, or return to the SQL Constraints overview.

A PRIMARY KEY is the definitive identifier for every row in a table.

It ensures no duplicate values exist and that the identifying column never stores NULL. Because of these guarantees, primary keys become the foundation for relationships, indexes, and fast lookups.

What a Primary Key Does

A primary key enforces two rules at once:

Every value is unique
No value is NULL

Example:

sql exampleWoHoTech

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

StudentID now uniquely identifies every student.

Defining a PRIMARY KEY

You can define the primary key inline:

sql exampleWoHoTech

StudentID INT PRIMARY KEY

Or as a table constraint:

sql exampleWoHoTech

CREATE TABLE Students (
    StudentID INT,
    Name VARCHAR(100),
    PRIMARY KEY (StudentID)
);

The table-level form is required when using composite primary keys.

Composite Primary Key

A single column does not always provide enough uniqueness. A composite primary key uses two or more columns together.

sql exampleWoHoTech

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

The pair (StudentID, CourseID) must be unique, but StudentID can appear in other courses.

Real-World Uses

Primary keys appear in nearly every table:

Table	Typical Primary Key
Users	UserID
Orders	OrderID
Products	ProductID
Employees	EmployeeID

Common naming conventions are id, user_id, order_id, or employee_id.

Pseudocode example:

If User exists: For each row in Users: Print UserID and Name Query orders: Join Orders ON Orders.UserID = Users.UserID

Primary Key vs UNIQUE

Property	PRIMARY KEY	UNIQUE
Allows NULL?	No	Yes (limited)
How many per table?	One	Multiple
Creates index?	Yes	Yes
Identifies row?	Yes	No

Use a primary key for row identity. Use UNIQUE when you only need to prevent duplicates.

Altering Primary Keys

sql exampleWoHoTech

ALTER TABLE Students
ADD PRIMARY KEY (StudentID);

sql exampleWoHoTech

ALTER TABLE Students
DROP PRIMARY KEY;

Common Mistakes

Choosing a volatile column as a primary key Use a key that will never change. A student's StudentID is stable; their email or phone number might change.

Using natural keys when a surrogate is better A composite of first name and last name is not a good primary key because names can collide or change. An auto-incrementing id is safer.

Frequently Asked Questions

Q: Does a primary key always have to be an integer? A: No. Strings, UUIDs, and composite keys also work. Integer auto-increment keys are the most common because they are small, fast, and simple.

Q: Can I update a primary key value? A: Yes, but avoid it. Changing a primary key breaks dependent foreign keys unless the change cascades.

Summary

A primary key uniquely identifies each row.
It automatically enforces NULL and duplicate restrictions.
Use it as the anchor for relationships with other tables.
Choose values that are stable and permanent.

Next Step

See FOREIGN KEY to learn how tables relate to each other, or return to the SQL Constraints overview.

PRIMARY KEY

What a Primary Key Does

Defining a PRIMARY KEY

Composite Primary Key

Real-World Uses

Primary Key vs UNIQUE

Altering Primary Keys

Common Mistakes

Frequently Asked Questions

Summary

Next Step

Continue learning this concept

PRIMARY KEY

What a Primary Key Does

Defining a PRIMARY KEY

Composite Primary Key

Real-World Uses

Primary Key vs UNIQUE

Altering Primary Keys

Common Mistakes

Frequently Asked Questions

Summary

Next Step

Continue learning this concept