Everything about Primary Key in MySQL [with Examples]

Summary: In this tutorial, we will discuss what is the primary key in MySQL? How to create a table with the primary key? and How to add and remove a primary key constraint from an existing table?

what is a Primary Key?

A Primary Key in MySQL is a column or set of columns (attributes) that uniquely identifies a row (a record).

Example:

Every student in a class has a unique roll number, so while creating table ‘math_marks’ to store the math score of all students ‘roll_no’ should be the primary key because ‘name’ of two students could be the same in one class but not the roll number.

Similarly, we can use a combination of multiple columns as the primary key.

In this table, ‘roll_no’ and ‘name’ together forms a primary key (i.e {roll_no, name}). This is valid as long as the combination is unique throughout the table.

Some of the important properties of the primary key in MySQL are

• A primary key is a constraint that restricts columns to have a unique set of values.
• A table can have a maximum of one primary key (Either a single column as a primary key or set of columns as a primary key).
• Columns declared as the primary key cannot have NULL value.
• Columns declared as the primary key must have unique values throughout the table.

Create New Table with Primary Key

The general syntax to create a table with a single column as the primary key is

CREATE TABLE table_name (
    column data_type PRIMARY KEY,
    ...
);

and syntax to create a table with a set of columns as the primary key is

CREATE TABLE table_name (
    column_1 data_type,
    column_2 data type,
    ...
    PRIMARY KEY (column_1, column_2)
);

Let’s see some examples for a better understanding.

Example 1: Table with a single column as a primary key

create table math_marks (
roll_no int PRIMARY KEY, 
name varchar(30) NOT NULL, 
marks int default 0
);

Specifying roll_no as a primary key means roll_no cannot be null and each roll_no value is unique i.e each record in the table must have a unique roll_no.

Example 2: Table with multiple columns as a primary key

create table math_marks (
roll_no int, 
name varchar(30), 
marks int default 0, 
PRIMARY KEY (roll_no, name)
);

In the above table both roll_no and name together comprise a single primary key for math_marks.

Add Primary key to Existing Table

Sometimes we create a table without a primary key or want to add the primary key constraint to the existing table. In that case, we should use the following syntax in MySQL to add a primary key.

ALTER TABLE table_name  
ADD PRIMARY KEY(column_1, column_2, ...);

Example 1: Adding a primary key to the single column of an existing table ‘student’.

Before:

ALTER TABLE students
ADD PRIMARY KEY (roll_no);

After:

Example 2: Similarly let’s add a primary key constraint to set of columns of the same old table.

Before:

ALTER TABLE students 
ADD PRIMARY KEY (roll_no, name);

After:

Remove or Drop Primary Key from an Existing Table

The general syntax to remove a primary key constraint from a table in MySQL is

ALTER TABLE table_name 
DROP PRIMARY KEY;

Example: Removing the primary key from columns of the same old table to which we had added constraint before.

Before:

ALTER TABLE students 
DROP PRIMARY KEY;

After:

As you can notice, executing the above alter statement ‘PRIMARY KEY’ constraint has been removed but ‘NOT NULL’ constraints persisted which was initially added along with the primary key constraint (because of the fact that primary key columns cannot be NULL).

Note: Make sure to replace table_name with the name of your MySQL table name before executing the SQL statements.