Changing a Primary Key to a Composite Key in TDSQL for MySQL: Dealing with sql_require_primary_key

Problem Background A colleague needed to change a table's primary key in TDSQL (for MySQL) to a composite key and wondered whether the existing primary key must be dropped first. Attempting alter table test drop primary key; produced error 3750 because the system variable sql_require_primary_key was set, which enforces the presence of a primary key.

Problem Analysis When sql_require_primary_key is ON, MySQL disallows creating or altering a table without a primary key. The variable can be changed dynamically. Lack of a primary key may also cause performance issues in row‑based replication.

参数名称：sql_require_primary_key
作用范围：Global & Session
动态修改：Yes
默认值：OFF
该参数设置为 ON 时，SQL 语句 create table 创建新表或者 alter 语句时，对已存在的表进行修改，将会强制检查表中是否包含主键，如果没有主键，则会报错。

The question is whether the primary key can be directly altered to a composite key.

Solution 1 – Temporarily Disable the Variable Turn off sql_require_primary_key , drop the existing primary key, then re‑enable the variable after adding the new composite key.

[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.

show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | ON    |
+-------------------------+-------+

set sql_require_primary_key = OFF;
Query OK, 0 rows affected (0.02 sec)

show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF   |
+-------------------------+-------+

alter table t_primary_key drop primary key;
Query OK, 0 rows affected (0.10 sec)

This approach works but carries a risk: between dropping the old key and creating the new one, duplicate values could cause the new key creation to fail, and the variable must be restored to its original state.

Solution 2 – Combine Drop and Add in One Statement Keep sql_require_primary_key ON and execute a single ALTER statement that drops the old primary key and adds the new composite key.

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)

This method avoids the window without a primary key and preserves the variable setting.

Conclusion MySQL’s fine‑grained parameter control allows multiple ways to address the need to change a primary key to a composite key. Understanding the role of sql_require_primary_key and choosing the appropriate method—either temporarily disabling the variable or using a combined ALTER statement—ensures a safe and efficient migration.