Removing the Primary Key in a MySQL Table

1. Overview

A PRIMARY KEY is a table constraint in MySQL. We can define a PRIMARY KEY on one or more columns. A PRIMARY KEY uniquely identifies a row of data in a database table. Furthermore, we can’t add NULL values to a PRIMARY KEY. When we use the InnoDB storage engine, which’s the default, MySQL automatically associates a PRIMARY KEY with an index called PRIMARY that helps in fast lookups of row data. As a result, a PRIMARY KEY improves query performance.

In this tutorial, we’ll learn why we may need to remove a PRIMARY KEY in MySQL, and how we can remove it. At the outset, when we remove a PRIMARY KEY we don’t remove the column/s on which it’s defined. To demonstrate, we’ll use the Registration table from the University database.

2. Setting Up Prerequisite Schemas

Let’s set up the sample schemas provided on GitHub. We can find the existing constraints in a table by querying the INFORMATION_SCHEMA:

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'registration';
+------------------------------+-----------------+
| CONSTRAINT_NAME              | CONSTRAINT_TYPE |
+------------------------------+-----------------+
| id                           | UNIQUE          |
| PRIMARY                      | PRIMARY KEY     |
| registration_course_id_fkey  | FOREIGN KEY     |
| registration_student_id_fkey | FOREIGN KEY     |
+------------------------------+-----------------+

Additionally, let’s create a custom table called Department_Duplicate with only a PRIMARY KEY constraint:

CREATE TABLE Department_Duplicate(
    id INT PRIMARY KEY,
    name VARCHAR (50),
    code VARCHAR (4)
 );

INSERT INTO Department_Duplicate(id,name,code) 
VALUES(1,'Data Science','DS');

Let’s query the INFORMATION_SCHEMA again to verify:

+-----------------+-----------------+
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
+-----------------+-----------------+

Having just a PRIMARY KEY constraint helps in demonstrating its removal.

3. Why Remove a PRIMARY KEY?

To begin with, let’s explore why we may need to remove a PRIMARY KEY even though it has many benefits.

3.1. To Define a New Primary Key

One of the reasons we may want to remove an existing PRIMARY KEY is that we want to define a new PRIMARY KEY on different column/s. We can define only one PRIMARY KEY in a table; therefore, we need to remove the existing PRIMARY KEY before defining a new one. To demonstrate this, let’s try to create a second primary key:

ALTER TABLE REGISTRATION 
ADD PRIMARY KEY (course_id);
ERROR 1068 (42000): Multiple primary key defined

An error results; therefore, we must remove the existing PRIMARY KEY if we want to add a new one.

3.2. To Allow Duplicate Values

Another reason we may want to remove a PRIMARY KEY is that we want to allow duplicate values in the column/s on which a PRIMARY KEY is defined. This is because a PRIMARY KEY doesn’t allow duplicate values. Let’s try adding a duplicate value to the id column, which is the PRIMARY KEY column, in the Department_Duplicate table:

INSERT INTO Department_Duplicate(id,name,code) 
VALUES(1,'Data Science','DS');
ERROR 1062 (23000): Duplicate entry '1' for key 'department_duplicate.PRIMARY'

We find that we can’t add a duplicate value. Further, removing a PRIMARY KEY by itself may not allow duplicate values if it leads to data, or referential, integrity issues.

3.3. To Allow Null Values

We may want to allow NULL values in the column/s on which a PRIMARY KEY is defined. For this, we need to remove the PRIMARY KEY because a PRIMARY KEY doesn’t allow NULL values. Let’s try adding a NULL value to the id column:

INSERT INTO Department_Duplicate(id,name,code) 
VALUES(NULL,'Data Science','DS');
ERROR 1048 (23000): Column 'id' cannot be null

We can’t add a null value. Further, removing a PRIMARY KEY by itself may not allow null values if it leads to data, or referential, integrity issues.

Let’s try modifying the id column definition to allow NULL values:

ALTER TABLE Department_Duplicate 
MODIFY id INT NULL;
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; 
if you need NULL in a key, use UNIQUE instead

We can’t alter the PRIMARY KEY to make it nullable.

4. Dropping a PRIMARY KEY

To begin with, let’s verify that the PRIMARY KEY exists in the Department_Duplicate table:

DESC Department_Duplicate;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| code  | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Indeed, the Key column in the result lists the id column as PRI. A value of PRI implies that the column is the PRIMARY KEY or one of the columns in the PRIMARY KEY.

Let’s drop the PRIMARY KEY:

ALTER TABLE Department_Duplicate 
DROP PRIMARY KEY;

Afterward, we can add duplicate values to the id column:

INSERT INTO Department_Duplicate(id,name,code) 
VALUES(1,'Data Science','DS');

Further, let’s make the id column nullable and add null values:

ALTER TABLE Department_Duplicate 
MODIFY id INT NULL;

INSERT INTO Department_Duplicate(id,name,code) 
VALUES(NULL,'Data Science','DS');

Let’s verify that the duplicate and NULL values are added:

SELECT * from Department_Duplicate;
+------+--------------+------+
| id   | name         | code |
+------+--------------+------+
|    1 | Data Science | DS   |
|    1 | Data Science | DS   |
| NULL | Data Science | DS   |
+------+--------------+------+

Let’s verify the PRIMARY KEY has been dropped:

DESC Department_Duplicate;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| code  | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Indeed, the Key result column doesn’t list PRI for the id table column. Additionally, the Null result column lists YES for the id column, which means that the table column is nullable.

We can’t undo the removal of the PRIMARY KEY. This is because the ALTER TABLE statement causes an implicit commit. However, if one of the other operations performed in the same ALTER TABLE statement fails, the statement fails as a whole, and it does not remove the PRIMARY KEY.

5. Dropping a Primary Key in a Table With a UNIQUE Key

We can drop a PRIMARY KEY in a table that also has a UNIQUE key defined. Let’s drop the PRIMARY KEY in the Registration table, which also has a UNIQUE key defined:

ALTER TABLE REGISTRATION 
DROP PRIMARY KEY;

It drops the PRIMARY KEY, but may still list a PRI Key.

DESC REGISTRATION;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | NO   | PRI | NULL    |       |
| semester     | varchar(30) | YES  |     | NULL    |       |
| year         | int         | YES  |     | NULL    |       |
| reg_datetime | datetime    | YES  |     | NULL    |       |
| course_id    | varchar(10) | YES  | MUL | NULL    |       |
| student_id   | int         | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

This is because MySQL may display a UNIQUE key/index that can’t contain NULL values as PRI, especially if there’s no PRIMARY KEY in the table. Let’s run the same SQL query on the INFORMATION_SCHEMA to verify:

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'registration';
+------------------------------+-----------------+
| CONSTRAINT_NAME              | CONSTRAINT_TYPE |
+------------------------------+-----------------+
| id                           | UNIQUE          |
| registration_course_id_fkey  | FOREIGN KEY     |
| registration_student_id_fkey | FOREIGN KEY     |
+------------------------------+-----------------+

Indeed, it doesn’t list a PRIMARY KEY.

Furthermore, we can make the id column nullable:

ALTER TABLE REGISTRATION 
MODIFY id INT NULL;

Thereafter, call DESC to list the table description:

desc registration;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | YES  | UNI | NULL    |       |
| semester     | varchar(30) | YES  |     | NULL    |       |
| year         | int         | YES  |     | NULL    |       |
| reg_datetime | datetime    | YES  |     | NULL    |       |
| course_id    | varchar(10) | YES  | MUL | NULL    |       |
| student_id   | int         | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Indeed, the Key column in the result lists UNI instead of PRI for the nullable column id.

6. Conclusion

In this article, we learned about dropping a PRIMARY KEY in a MySQL database. Further, we showed why we may need to drop/remove a PRIMARY KEY. We drop a PRIMARY KEY without dropping the column/s the PRIMARY KEY is defined on. We can require a PRIMARY KEY by setting the system variable sql_require_primary_key to ON at the Session, or Global level. When a PRIMARY KEY is made a requirement it can’t be dropped.