How to Modify Primary Key in MySQL Without Errors: A Comprehensive Guide

1. Background

A colleague asked a question: when modifying a table in MySQL to use a composite primary key, must the existing primary key be deleted first? When attempting to delete the primary key, the following error occurs:

[test]> alter table test drop primary key;
ERROR 3750 (HY000): Unable to create or change a table without a primary key,
when the system variable 'sql_require_primary_key' is set.
Add a primary key to the table or unset this variable to avoid this message.
Note that tables without a primary key can cause performance problems in row-based replication,
so please consult your DBA before changing this setting.

‍

2. Problem Analysis

From the error message, the specific cause is clear: when the sql_require_primary_key parameter is set, you cannot create or modify a table without a primary key. The solution is to add a primary key or remove this parameter to avoid the error. Additionally, a warning is provided that tables without primary keys may cause performance issues in row-based replication.

The sql_require_primary_key parameter controls the enforcement of primary key checks and can be dynamically modified.

Parameter Name: sql_require_primary_key

Scope: Global & Session

Dynamically Modifiable: Yes

Default Value: OFF

When this parameter is set to ON, SQL statements like CREATE TABLE for creating new tables or ALTER TABLE for modifying existing tables will enforce a check to ensure the table contains a primary key. If no primary key exists, an error will be reported.

In this scenario, is it possible to modify the primary key to a composite primary key?

Create a test table with the initial primary key as id:

bisal@mysqldb: [test]> create table t_primary_key (id int, c1 varchar(1), c2 varchar(1), constraint pk_t_id primary key(id));
Query OK, 0 rows affected (0.07 sec)

‍

3. Solutions

3.1 Solution One

Since the sql_require_primary_key parameter controls the enforcement of primary key checks and is dynamically modifiable, you can temporarily disable it and then re-enable it.

bisal@mysqldb: [test]> alter table t_primary_key drop primary key;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message.

bisal@mysqldb: [(none)]> show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF   |
+-------------------------+-------+
1 row in set (0.00 sec)

bisal@mysqldb: [(none)]> set sql_require_primary_key = ON;
Query OK, 0 rows affected (0.02 sec)

bisal@mysqldb: [(none)]> show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

bisal@mysqldb: [test]> alter table t_primary_key drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

‍

However, there is a potential risk: during the time between deleting the primary key and creating a new one, if duplicate data is inserted into the primary key fields, it may cause the creation of the new primary key to fail. Additionally, since the parameter is set to a non-default value, remember to change it back after creating the primary key.

3.2 Solution Two

If sql_require_primary_key is set to ON, it means that the table must always have a primary key and cannot be left in a vacuum. The operation of changing the primary key actually involves two steps: deleting the original primary key and creating a new one. Therefore, you can combine these two steps into a single statement.

MySQL supports executing multiple statements at once, so you can merge alter table ... drop primary key and add constraint ... primary key ... into one statement:

bisal@mysqldb: [test]> alter table t_primary_key drop primary key, add constraint pk_t_01 primary key (id, c1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

‍

Summary

From this issue, it is evident that MySQL's parameter control is very granular. However, with various methods, you can solve specific scenario problems. The key is to fully understand the meaning of the parameters and the requirements of the scenario to find the appropriate solution.

‍