How to Update Primary Key in MySQL Tables

1. Update Primary Key in MySQL Tables
2. Drop the Existing Primary Key and Make a New Primary Key Using a Different Column in MySQL
3. Update the Number of Columns Involved in Constructing the Primary Key in MySQL
4. Update MySQL Primary Key By Changing Its Data Type

This tutorial teaches how to update the primary key in MySQL tables. We’ll be using the ALTER command to make any change in the primary key.

Update Primary Key in MySQL Tables

There are various scenarios where we can update the primary key in MySQL Tables. Let’s see each of them below.

1. Drop the existing primary key and make a new primary key using a different column.
2. Update the number of columns involved in constructing the primary key.
3. Change the data type of the primary key.

We can use the ALTER command that will be practically demonstrated later in this tutorial to change the primary key.

Drop the Existing Primary Key and Make a New Primary Key Using a Different Column in MySQL

Suppose we have a users table in the test database whose primary key is the ID attribute. For some reason, we want to drop it and construct a new primary key using the USERNAME column.

Create users Table:

#create a table named 'users' in 'test' database
CREATE TABLE `test`.`users` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

Use the following query to check the table definition.

SHOW CREATE TABLE test.users;

It will show the following output; your results may differ if you use a different table.

Output:

To update the primary key from ID to the USERNAME field, we must drop the AUTO_INCREMENT from the ID attribute first; otherwise, it will generate an error.

Drop AUTO_INCREMENT for ID field:

# Disable foreign key check
SET FOREIGN_KEY_CHECKS=0;
# Modify the `ID` attribute
ALTER TABLE test.users MODIFY COLUMN ID INT NOT NULL;
# Enable foreign key check
SET FOREIGN_KEY_CHECKS=1;

Use SHOW CREATE TABLE test.users; to confirm that AUTO_INCREMENT is dropped.

Output:

Update Primary Key:

ALTER TABLE test.users DROP PRIMARY KEY, ADD PRIMARY KEY(USERNAME);

Use SHOW CREATE TABLE test.users; again to ensure that the primary key is USERNAME.

Output:

Update the Number of Columns Involved in Constructing the Primary Key in MySQL

We are moving ahead with the users table we created in the previous section. The primary key was the USERNAME column which can be easily checked using the following query.

SHOW CREATE TABLE test.users;

We use the ALTER command to update the primary key that will consist of two columns now, ID and USERNAME.

ALTER TABLE test.users DROP PRIMARY KEY, ADD PRIMARY KEY(ID, USERNAME);

We can confirm the updated primary key by using the following query.

SHOW CREATE TABLE test.users;

Output:

Update MySQL Primary Key By Changing Its Data Type

Here, we create another table and name it user where the primary key is the ID field of the INT type.

CREATE TABLE `test`.`user` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

We can use the following queries to update the data type from INT to BIGINT of an existing primary key.

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE test.users MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS=1;

Confirm the changes by using a query which is given below.

SHOW CREATE TABLE test.user;

Output:

Updating the primary key in the MySQL table is not difficult before making relationships in the database but highly discouraged after establishing the relationships in the database for the following basic reasons.

1. If you got the idea to change the primary key in the running database, you probably have chosen the incorrect field for the primary key. So be extra careful about selecting the fields as a primary key.

2. You will be required to drop that record on which you are trying to change the primary key. You may have to lose all the relationships for that particular record.

   If you change the primary key, you have to add that record and create relationships again.

3. If you change the primary key from one column to three columns, the new primary key (consisting of three columns) must be used as a foreign key in all other related tables. Remember, this can impact storage, performance, and design.

4. Any change in the primary key is never encouraged within the database except if you re-construct the database during MIGRATION or FILE RE-ORGANIZATION. But, you have to be extra careful because these primary keys might be used as foreign keys in other tables.