As MySQL database administrators, we are well aware of the significance of implementing a primary key within a table. Throughout our careers, most of us have encountered situations where the absence of a primary key has led to operational challenges. Primary keys play an indispensable role in sound database design by uniquely identifying individual rows and significantly enhancing data retrieval, manipulation, and overall system performance.
From the MySQL documentation:
The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value.
It is common for tables to be inadvertently created without a primary key, often leading to regrettable consequences that we only recognize when issues arise. One prominent issue we face is replication delay in row-based replication. When a delete or update operation is executed on the primary database, affecting multiple rows, it may execute swiftly on the primary server. However, this action can introduce delays on replica servers due to the need to scan each modified row. Even a powerful tool like pt-online-schema-change refuses to function without a primary key or unique key, which can be problematic when you need to make alterations to a table without disrupting read and write operations.
To mitigate such scenarios and the ensuing frustrations, MySQL has introduced a valuable feature in the form of the ‘sql_require_primary_key’ variable. This feature was introduced in MySQL 8.0.13 and serves as a safeguard against the creation of tables without a primary key. By default, this variable is set to OFF, allowing users to create tables without a primary key if they choose to do so. However, both session-level and global-level configurations are available for this variable.
To enable the ‘sql_require_primary_key’ variable, your MySQL version should be 8.0.13 or a later release. It is a dynamic variable that can be adjusted at both the global and session levels. By default, this variable remains inactive, as we can observe in this demonstration using MySQL version 8.0.25.”
As we can see, by default, the tsql_require_primary_key variable is disabled.
1 2 3 4 5 6 7 8 9 10 11 12 | mysql1 > select @@version; +-----------+ | @@version | +-----------+ | 8.0.25 | +-----------+ mysql1 > select @@sql_require_primary_key; +---------------------------+ | @@sql_require_primary_key | +---------------------------+ | 0 | +---------------------------+ |
I have created a table named students, which doesn’t have the primary key on it
1 2 3 4 5 6 7 8 9 | mysql1 > show create table studentsG *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `id` int unsigned NOT NULL, `name` varchar(30) NOT NULL, `Branch` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) |
I have enabled the sql_require_primary_key and see how this affects the operations.
1 2 3 4 5 6 | mysql1 > select @@sql_require_primary_key; +---------------------------+ | @@sql_require_primary_key | +---------------------------+ | 1 | +---------------------------+ |
Creating a table without Primary_key fails with an error.
1 2 3 4 5 6 | mysql1 > CREATE TABLE `employee` ( -> `id` int unsigned NOT NULL AUTO_INCREMENT, -> `name` varchar(30) NOT NULL, -> `place` varchar(30) NOT NULL -> ) ENGINE=InnoDB; 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. |
The next intriguing question to ponder is the fate of tables that already exist without a primary key. To shed light on this, let’s consider a table called “students” that already resides on the node and carry out some operations on it.
The INSERT/UPDATE/DELETE works without any errors or warnings on the table, but the ALTER statement will fail on the table with an error.
1 2 3 4 5 6 7 8 9 10 11 12 | mysql1 > INSERT INTO students VALUES (5, 'Lilly', 'CCE'); Query OK, 1 row affected (0.00 sec) mysql1 > UPDATE students set Branch='CSE' where name='lilly'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql1 > DELETE from students where id=5; Query OK, 1 row affected (0.00 sec) mysql1 > ALTER TABLE students ADD COLUMN dob int NOT NULL; 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. |
Replication considerations
In a source replica configuration, the behavior of ‘sql_require_primary_key’ is influenced by the ‘REQUIRE_TABLE_PRIMARY_KEY_CHECK’ parameter in the ‘CHANGE MASTER TO/CHANGE REPLICATION SOURCE’ statement, which empowers a replica to determine its own policy regarding primary key checks. By default, this parameter is set to ‘STREAM,’ causing the replica to adopt the primary key check value replicated from the source for each transaction.
Let’s test this. I have disabled the sql_require_primary_key on the source, enabled it on replica, and created a table without the primary key, REQUIRE_TABLE_PRIMARY_KEY_CHECK is set to STREAM by default, and you can see slave was able to replicate the table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | mysql1 > select @@sql_require_primary_key; +---------------------------+ | @@sql_require_primary_key | +---------------------------+ | 0 | +---------------------------+ mysql_s1 > show variables like '%prima%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | sql_require_primary_key | ON | +-------------------------+-------+ mysql1 > CREATE TABLE `hotels` ( -> `id` int unsigned NOT NULL, -> `name` varchar(30) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql_s1 > show create table test.hotelsG *************************** 1. row *************************** Table: hotels Create Table: CREATE TABLE `hotels` ( `id` int NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
If ‘REQUIRE_TABLE_PRIMARY_KEY_CHECK’ is configured as ‘ON,’ the replica consistently enforces the ‘sql_require_primary_key’ system variable with the ‘ON’ value during replication operations, mandating the presence of a primary key.
Conversely, when ‘REQUIRE_TABLE_PRIMARY_KEY_CHECK’ is set to ‘OFF,’ the replica consistently uses the ‘OFF’ value for the ‘sql_require_primary_key’ system variable in replication operations, ensuring that a primary key is never required, even if the source initially mandated one.
Let’s test this. I have disabled the sql_require_primary_key on source, REQUIRE_TABLE_PRIMARY_KEY_CHECK is set to OFF in CHANGE REPLICATION SOURCE, and I disabled the sql_require_primary_key on replica and created a table on source without primary_key, and it caused this replication error on the replica. So it is better and recommended not to run any changes directly on replicas.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql_s1 > STOP REPLICA; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql_s1 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF ; Query OK, 0 rows affected (0.01 sec) mysql_s1 > STOP REPLICA; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql_s1 > select @@sql_require_primary_key; +---------------------------+ | @@sql_require_primary_key | +---------------------------+ | 0 | +---------------------------+ mysql1 > CREATE TABLE `test_3` ( `id` int unsigned NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Last_SQL_Error: Error '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.' on query. Default database: 'test'. Query: 'CREATE TABLE `test_3` ( `id` int NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB' |
Conclusion
The “sql_require_primary_key” function, which was added to MySQL 8.0.13, is a useful addition that allows database administrators to ensure that tables are created exclusively with the primary key. Enforcing the presence of primary keys promotes better database design and minimizes the potential pitfalls associated with missing primary keys. With this feature, MySQL encourages users to adopt best practices, leading to more robust and efficient database systems.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
