MySQL Bugs: #85151: Implement new REJECT_CREATE_TABLE_WITHOUT

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:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

and what about dropping of existing primary keys via alter table..

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.

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. 

Thanks!

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

regards,
Umesh