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

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 better understanding.

Example 1: Table with single column as a primary key

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

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

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,

MySQL table

ALTER TABLE students
ADD PRIMARY KEY (roll_no);

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

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

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

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,

multiple column primary key

ALTER TABLE students 
DROP 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