In general, once we create a primary key on a table we leave it there. It’s there to help us maintain data integrity and removing it can put that data integrity at risk.

But sometimes we might have good reason to remove a primary key. Perhaps we need to drop it so that we can create a primary key on a different column or multiple columns. Or perhaps there’s another valid reason to drop the primary key.

Either way, below is an example of dropping the primary key from a column.

Syntax

We use the ALTER TABLE statement to drop the primary key.

It goes something like this:

ALTER TABLE table_name DROP PRIMARY KEY;

All we do is replace table_name with the actual name of the table, and the primary key will be dropped.

Example

Suppose we create a table like this:

CREATE TABLE Person
(
    PersonId INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

We made the PersonId the primary key of this table.

We can verify this with the following code:

SHOW KEYS FROM Person 
WHERE Key_name = 'PRIMARY';

Result:

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Person |          0 | PRIMARY  |            1 | PersonId    | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

This tells us that the PersonId column is the primary key.

Drop the Primary Key

Now let’s drop the primary key:

ALTER TABLE Person DROP PRIMARY KEY;

Result:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

The primary key has now been dropped.

We can use SHOW KEYS to check it again:

SHOW KEYS FROM Person 
WHERE Key_name = 'PRIMARY';

Result:

Empty set (0.01 sec)

An empty set is returned as expected because the table no longer has a primary key.