Bug #69223 Give a warning on CREATE TABLE without PRIMARY KEY
Submitted: 14 May 2013 6:22 Modified: 10 Jul 18:02
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.6.10, 5.6.26, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: primary key, warning
Triage: Needs Triage: D5 (Feature request)

[14 May 2013 6:22] Daniël van Eeden
Description:
It's easy to create a table without primary key by accident. The result can be bad performance for InnoDB and replication and possible other issues. To add a key after the application has been created can be troublesome as it could break backwards compatibility as it might be required to add a surrogate primary key (id (big)int auto_increment).

http://www.fromdual.com/disadvantages-of-explicitly-not-using-innodb-primary-keys
http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/

How to repeat:
Create a table without primary key. Then check if there are any warnings.

Suggested fix:
It would be good if a developer would get a warning when he/she creates a table without primary key. Optionally it could be changed to an error.

SET GLOBAL sql_mode='WARNING_FOR_NON_PK_CREATE_TABLE';
[24 Jun 2013 20:03] Miguel Solorzano
Thank you for the bug report.
[30 Oct 2015 8:35] Daniël van Eeden
updated version, tags
[31 Oct 2015 20:53] Ovais Tariq
As noted here not having a primary key can have performance implications at enough concurrency:
https://www.percona.com/blog/2013/10/18/innodb-scalability-issues-tables-without-primary-k...

It would be great to have MySQL generate a warning when tables are created without Primary Keys
[6 May 2016 13:07] Simon Mudd
I think it should be more than that. MySQL should optionally FORCE this.
See: http://blog.wl0.org/2016/05/lossless-rbr-for-mysql-8-0/

So maybe 3 states: do nothing, generate warning , generate error, and by default in 8.0+ make the behaviour generate an error.
[6 May 2016 13:24] Simon Mudd
One final thought. sql_modes cause all sorts of confusion and pain. It would seem better to me to have a new global variable which controls this behaviour.

In a replicated environment preventing this on a master should prevent it getting downstream to the slaves, so the configuration on the slave should not necessarily _need_ to be as strict.
[21 Dec 2016 8:27] Daniël van Eeden
Note that group replication requires primary keys (or a non-nullable unique key).

http://lefred.be/content/mysql-group-replication-and-table-design/
[22 Jun 2017 13:54] Jean-François Gagné
Related: Bug#69845.
[12 Apr 10:30] Daniël van Eeden
I wrote a MySQL audit plugin to prevent creation of tables without primary key:
https://github.com/dveeden/mysql-enforcepk
[10 Jul 18:02] Paul Dubois
Posted by developer:
 
Fixed in 8.0.13.

The new sql_require_primary_key system variable makes it possible to
have statements that create new tables or alter the structure of
existing tables enforce the requirement that tables have a primary
key. Enabling this variable helps avoid performance problems in
row-based replication that can occur when tables have no primary key.
Suppose that a table has no primary key and an update or delete
modifies multiple rows. On the master server, this operation can be
performed using a single table scan but, when replicated using
row-based replication, results in a table scan for each row to be
modified on the slave. With a primary key, these table scans do not
occur.