Bug #69223 Give a warning on CREATE TABLE without PRIMARY KEY
Submitted: 14 May 2013 6:22 Modified: 21 Dec 2016 8:27
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified 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:
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] Godofredo 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 13:54] Jean-François Gagné
Related: Bug#69845.