A unique constraint specifies,
one or more columns as unique it identifies.
It is satisfied only when no two rows store
the same non-null values at its core.A primary key constraint is a unique one that will say
PRIMARY KEY in its defined way.
It is satisfied only when rows unfold,
and none may be null in the columns they hold.
The ISO/IEC 9075 Unique and Primary Keys Poem
Pep Pla & ChatGPT
What’s in a Primary Key?
The ISO standard defines primary keys as unique keys that can’t store null values. There is no difference between a primary key and a unique key on columns that can’t contain null values. There is only one restriction: you can define just one primary key per table, while you may have any number of unique keys with or without null values.
Primary keys are extremely useful, and I’m not going to go over the benefits of primary keys here. However, in some cases, they are more than helpful; they are required to maintain performance. MySQL replication is one such case, because it is logical replication, even when using the row-based binary log format. Primary keys are also a requirement for Percona XtraDB Cluster and Group Replication for the same reasons.
MySQL 8.0.13 introduced the variable *sql_require_primary_key* to enforce the presence of primary keys on all tables created. However, this variable does not verify if tables that existed previously were created with a primary key. The parameter also requires you to specify the primary key, not a unique key on non-nullable columns.
Trust Unique Keys not bearing Null Values
But what happens when you have tables that have a unique key on non-nullable columns? Well, the problem here is that, even if those tables have a non-nullable unique key, since there is no actual primary key defined, actions like running pt-online-schema-change, gh-ost, or just “create table like” will fail. You will need to set sql_require_primary_key to off on the sessions that run those commands.
Wouldn’t it be great if we could tell MySQL to rename that non-nullable unique key into a primary key? It would. But what if I tell you that that key is the primary key for InnoDB? For InnoDB, each table is a special index called the Clustered Index. The keys of that index are the primary keys, but if InnoDB is unable to find a primary key, it will use the “first UNIQUE index with all key columns defined as NOT NULL.” So, if your table has a unique index and all the columns of the index are not null, InnoDB will use that index as the primary key.
But, although InnoDB uses that index as the primary key, MySQL doesn’t recognize it and considers the table as one without a primary key.
The Sting
In theory, the only solution is to rebuild the table to change from a non-nullable unique key to a primary key. But we can trick MySQL into migrating an InnoDB table from NNUK to PK. I’ll show you how.
Following we have the table that we plan to migrate:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `sbtest4` ( `id` int NOT NULL, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `id2` int NOT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `id2` (`id2`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED |
As you can see, there is no primary key, but InnoDB should be using one of my unique keys as the primary key. Let’s try to find out which one:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT i.name FROM information_schema.innodb_indexes i INNER JOIN information_schema.innodb_tables t USING (table_id) WHERE type = 3 AND t.name = Concat(DATABASE(), '/', 'sbtest4'); +------+ | name | +------+ | id | +------+ 1 row in set (0.00 sec) |
What the previous query does is find which index in sbtest4 is of type 3, or which index is the clustered index, the index that InnoDB considers good enough to become a primary key. In this case, the index is id on the column with the same name.
Now, we will create a temporary table with the same structure as sbtest4 and replace the id index with the primary key.
|
1 2 3 4 5 6 |
CREATE TABLE sbtest4_new LIKE sbtest4; Query OK, 0 rows affected (0.02 sec) ALTER TABLE sbtest4_new DROP KEY id, ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Now, we have a table with the same structure as the original table, but with a primary key instead of a unique index. Now it is time to discard its tablespace.
|
1 |
ALTER TABLE sbtest4_new DISCARD TABLESPACE; |
And flush the previous table for export. This step blocks sbtest4, so this procedure must take place during a maintenance window.
|
1 |
FLUSH TABLE sbtest4 FOR EXPORT; |
We will execute the next operation in the operating system, but DO NOT CLOSE this MySQL connection as sbtest4 is locked, and we want it to remain like this.
|
1 2 |
cd /var/lib/mysql/sbtest mv sbtest4.ibd sbtest4_new.ibd |
What we’ve done now is to rename the IBD file from sbtest4 to sbtest4_new. Now, we may import the tablespace into sbtest4_new in a new connection.
|
1 2 3 4 5 6 7 8 9 10 |
USE sbtest; ALTER TABLE sbtest4_new IMPORT TABLESPACE; Query OK, 0 rows affected, 1 warning (0.30 sec) SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sbtest/sbtest4_new.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
This operation will return a warning, but we should have imported the table appropriately. You can verify it by accessing the table.
|
1 2 3 4 5 6 7 8 |
mysql> SELECT * FROM sbtest4_new LIMIT 10; +----+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+-----+ | id | k | c | pad | id2 | +----+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+-----+ | 1 | 2141642 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 | 0 | ... +----+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+-----+ 10 rows in set (0.00 sec) |
Now it is time to unlock sbtest4 and drop it, as it has no data file. To complete the migration, we rename the new table to sbtest4.
|
1 2 3 4 5 6 7 8 9 10 11 |
FLUSH TABLE sbtest4 FOR EXPORT; Query OK, 0 rows affected (0.01 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE sbtest4; Query OK, 0 rows affected (0.01 sec) mysql> RENAME TABLE sbtest4_new TO sbtest4; Query OK, 0 rows affected (0.01 sec) |
By running these steps, sbtest4 now has a primary key instead of a unique key on non-nullable columns.
Disclaimer
Two final notes:
- This is an unsupported procedure. Test it with your MySQL version and never perform a maintenance operation like this without a proper database backup.
- In a replication setup, you need to repeat the
flush tables for exportand the file rename on each replica before running thealter table import tablespace. Another option is to perform the operation in a rolling fashion withsql_log_binset to 0 to avoid writing the changes to the binlog.
About the Author
Pep PlaPep has been working with databases all his life. Born in a small village by the Mediterranean, he currently lives in Barcelona. He loves tech, traveling, good food, music and, all things NASA. He hates talking about himself in the third person and has a particular sense of humor. Happily married, he is the father of three boys and three cats.
