JIYIK CN >

This article explains how to update the primary key in a MySQL table. We will use the ALTER command to make any changes to the primary key.

Update the primary key in a MySQL table

We can update the primary key in a MySQL table in multiple scenarios. Let's look at each of them below.

1. Drop the existing primary key and create a new one using different columns.
2. Updates the number of columns involved in constructing the primary key.
3. Change the data type of the primary key.

We can change the primary key using the ALTER command which we will actually demonstrate later in this tutorial.

Drop existing primary key and create new primary key using different columns 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 delete it and construct a new primary key using the USERNAME column.

Create the user 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 statement to check the table definition.

SHOW CREATE TABLE test.users;

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

Output:

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

Remove AUTO_INCREMENT for the 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 has been removed.

Output:

Update the 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 continuing with the users table we created in the previous section. The primary key is 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 which will now consist of two columns, ID and USERNAME.

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

We can use the following query to confirm the updated primary key.

SHOW CREATE TABLE test.users;

Output:

Updating MySQL primary key by changing data type

Here we create another table, named user, with the primary key being the ID field of type INT.

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 update the data type of an existing primary key from INT to BIGINT using the following query.

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 using the query given below.

SHOW CREATE TABLE test.user;

Output:

Updating the primary key in a MySQL table before establishing relations in the database is not difficult, but it is highly discouraged after establishing relations in the database for the following basic reasons.

1. If you want to change the primary key in a running database, you may have chosen the incorrect field for the primary key. So be careful when choosing a field as the primary key.
2. You will need to delete the record whose primary key you are trying to change. You may have to lose all relationships for that particular record.

   If you change the primary key, you must add that record and recreate the relationship.

3. If you change the primary key from one column to three columns, the new primary key (composed of three columns) must be used as a foreign key in all other related tables. Keep in mind that this affects storage, performance, and design.
4. Unless you are rebuilding the database during a MIGRATION or FILE RE-ORGANIZATION, it is highly discouraged to make any changes to the primary keys in the database. However, you must be extra careful because these primary keys may be used as foreign keys in other tables.