How do you drop a primary key or foreign key from an existing table?

Difficulty: 5/10

ALTER TABLE, constraints, schema migration

Dropping Primary Keys and Foreign Keys in MySQL

MySQL allows you to remove primary keys and foreign keys using ALTER TABLE. The exact syntax differs depending on the type of key.

1. Drop a Primary Key

1
A table can only have one PRIMARY KEY.
2
Dropping it removes the uniqueness and NOT NULL enforcement on the primary key column(s).

Command

If the primary key column also has AUTO_INCREMENT, you must remove that attribute separately.

Example (with AUTO_INCREMENT)

2. Drop a Foreign Key

1
Dropping a foreign key requires its constraint name.
2
The foreign key name is NOT the column name — it is an internal constraint identifier.

Find the Foreign Key Name

Command to Drop Foreign Key

If the foreign key also created an index automatically, you may need to drop that index too.

Drop Associated Index (if needed)

3. When Do You Need to Drop Keys?

0-2 years experience

2-5 years experience