Bug #85151 Implement new REJECT_CREATE_TABLE_WITHOUT_PK sql mode
Submitted: 23 Feb 2017 13:06 Modified: 30 Nov 2021 13:32
Reporter: Fernando Ipar (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Options Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[23 Feb 2017 13:06] Fernando Ipar
I suggest the implementation of a new sql_mode option, that could be called REJECT_CREATE_tABLE_WITHOUT_PK and that, if set, causes CREATE TABLE statements to fail if no PRIMARY KEY or UNIQUE KEY is specified. The goal is to let DBAs configure systems to minimize the possibility of Innodb tables being created without a primary key. My personal goal is for Innodb to avoid the downsides of the hidden rowid column, but I think it could be a valid use case for any storage engine. 

How to repeat:
Since this is currently unimplemented, there is no way to repeat the behavior. 

Suggested fix:
Implement the suggested sql_mode, cause the matching CREATE TABLE statements to fail if the mode is used.
[23 Feb 2017 14:01] MySQL Verification Team
and what about dropping of existing primary keys via alter table..
[23 Feb 2017 14:05] Fernando Ipar
It could work for that too. I suppose it is valid to extend this (and therefore rename my suggested mode name) to cause any DDL statement that would result in a PK-less table to fail. 

The mode can be temporarily disabled whenever a PK needs to be changed for a table, so I don't see a problem with that.
[22 Oct 2018 20:16] Fernando Ipar
I think this is now implemented by https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_require_pr... so as far as I'm concerned this can be closed. 

[30 Nov 2021 13:32] MySQL Verification Team
Hello Fernando Ipar,

Thank you for the feature request.
As you rightly mentioned this is implemented hence closing this feature request since requested feature is implemented https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_require_pr...

8.0.13 change log - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html