Primary Key
Learn what a primary key is, the rules it enforces, and how to define one on a table to uniquely identify every row.
Introduction
Every table needs a reliable way to identify one specific row out of many. Two students could share the same name and even the same age, but they cannot share the same primary key — that is exactly what a primary key is for.
In this lesson, you will learn what a primary key is, the rules it must follow, and how to define one on a table.
- What a primary key is and what problem it solves.
- The two rules every primary key must follow.
- How to define a primary key inline or as a table constraint.
- What a composite primary key is.
What is a Primary Key?
A primary key is a column, or a set of columns, that uniquely identifies each row in a table. No two rows in the table can ever have the same primary key value, and every row must have one.
In the students table, the id column is a natural primary key: every student gets their own unique id number, and that id is how the database (and your application code) reliably refers to one exact student, even if two students happen to share the same name.
Rules of a Primary Key
A primary key must follow two strict rules, which MySQL enforces automatically on every insert and update.
Must Be Unique
No two rows can share the same primary key value.
Cannot Be NULL
Every row must have a value in the primary key column — it can never be left empty.
Because a primary key is automatically both unique and never NULL, defining PRIMARY KEY on a column already gives it the effect of a UNIQUE constraint plus a NOT NULL constraint combined.
Defining a Primary Key
There are two common ways to define a primary key: inline, right after the column definition, or as a separate table constraint at the end of the CREATE TABLE statement.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade VARCHAR(2)
);The same table can also be written using a table constraint, which lists PRIMARY KEY separately after all the columns. This form is required when the primary key spans more than one column.
CREATE TABLE students (
id INT,
name VARCHAR(50),
age INT,
grade VARCHAR(2),
PRIMARY KEY (id)
);Composite Primary Keys
Sometimes no single column is enough to uniquely identify a row, but a combination of columns is. A primary key made of more than one column is called a composite primary key.
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);Here, neither student_id nor course_id alone is guaranteed to be unique (a student can enroll in many courses, and a course has many students), but the combination of the two together is unique for each row. Composite keys are common when modeling relationships between two tables, and will come up again later in this course.
Worked Example: Primary Key on students
Let's see the PRIMARY KEY constraint in action by trying to insert a duplicate id into the students table.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade VARCHAR(2)
);
INSERT INTO students (id, name, age, grade) VALUES (1, 'Alex', 16, 'A');
INSERT INTO students (id, name, age, grade) VALUES (2, 'Sam', 17, 'B');
-- Attempting to reuse id 1
INSERT INTO students (id, name, age, grade) VALUES (1, 'Priya', 16, 'A');ERROR 1062 (23000): Duplicate entry '1' for key 'students.PRIMARY'MySQL rejects the third insert because id 1 is already used by Alex's row. This is the primary key doing exactly what it is meant to do — guaranteeing that every student has a distinct, reliable identifier.
Common Mistakes
- Choosing a column like name as a primary key, when real-world names are not guaranteed to be unique.
- Trying to insert NULL into a primary key column.
- Forgetting that a table can only have one PRIMARY KEY, even if it is composed of multiple columns.
- Defining a composite primary key when a single simple column (like an id) would have been enough.
Best Practices
- Give almost every table a simple, dedicated id column as its primary key.
- Avoid using real-world data (like names or emails) as a primary key, since it can change or repeat.
- Reserve composite primary keys for tables that represent a relationship between two other tables.
- Define the primary key when you first create the table, rather than adding it later.
Frequently Asked Questions
Can a table have more than one primary key?
No, a table can only have one PRIMARY KEY. However, that one primary key can consist of multiple columns, which is called a composite primary key.
What happens if I do not define a primary key at all?
MySQL will let you create the table, but you lose the guarantee that every row can be uniquely and reliably identified, which causes problems as the table grows.
Is a primary key the same as AUTO_INCREMENT?
No. PRIMARY KEY enforces uniqueness and non-NULL values. AUTO_INCREMENT is a separate feature that automatically generates the next number for you — the two are commonly used together but are not the same thing.
Can I change which column is the primary key later?
Yes, using ALTER TABLE, but you must first drop the existing primary key constraint before adding a new one, and the new column must satisfy the uniqueness and NOT NULL rules for every existing row.
Key Takeaways
- A primary key uniquely identifies every row in a table.
- A primary key value must be unique and can never be NULL.
- A primary key can be defined inline or as a separate table constraint.
- A composite primary key is made up of more than one column.
- The students table typically uses id as a simple, reliable primary key.
Summary
A primary key is the foundation of reliably identifying rows in a table. By guaranteeing uniqueness and disallowing NULL, it becomes the anchor that other tables and relationships can safely reference.
In this lesson, you learned what a primary key is, its two core rules, how to define one, and what a composite primary key looks like. Next, you will learn how a foreign key uses another table's primary key to link two tables together.
- You understand what a primary key does.
- You know the two rules every primary key must follow.
- You can define a primary key inline or as a table constraint.
- You understand what a composite primary key is.