In this post, 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?

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

Example:

MySQL table with single column as primary key

Every student in a class has a unique roll number, so while creating table ‘math_marks’ to store math’s 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

  • Primary key is a type of constraint.
  • A table can have 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 single column as the primary key is

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

Let’s see some examples for better understanding.

Example 1: Table with single column as a primary key

MySQL table with single column as primary key

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

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.

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

Before,

MySQL table

After,

single column primary key

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

Before,

MySQL table

After,

multiple column primary key

Remove or Drop Primary Key from an Existing Table

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

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

Before,

multiple column primary key

After,

MySQL table with no primary key

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.

If you have any doubts at any part of the article or have any suggestion then please comment below.

This Post Has 4 Comments

Leave a Reply