Another possible issue that could be faced when using tables without a PK on a TokuDB table : if you use UNCOMMITED-READ isolation level, you can hit a HA_ERROR_CRASHED error during certain operations : http://www.tokutek.com/2013/11/what-does-the-incorrect-key-file-for-table-error-mean/

Although I like the idea very much, some of the existing database deployments using InnoDB will be affected badly. I know at least one ERP company with huge number of tables in its multi-platform database, no single table has pk, but each of them have unique indexes that consists of not-null composite columns that are chosen by the InnoDB as the implicit pk. Since innoDB keeps the table sorted according to the primary key, and also since the columns of the primary keys are added to the columns of the secondary indexes, we have marked one of the normally not null columns to accept null values, that way forcing the addition of the documented 6-byte invisible pk column. That saved us and InnoDB is now handling successfully that bad database design.

Therefore, I am against this idea.

Forcing primary key will affect only new tables not the old ones. But, I would like to have an option e.g. innodb_force_pk with default ON, but option to set it dynamically OFF and with that option to create tables without primary key.

gokhan of course, the task is about adding an option, so that users could enable it if necessary. We wouldn't think about imposing this "only PK" limitation on all users and all applications.

Thanks for the clarification. +1 from me!

Hi,

In this description tables created without primary key should be disabled, however in link also unique keys are mentioned. Is the idea to disable create table without primary key or should table without primary key but with unique key accepted ?

R: Jan

unique index with nullable columns is not good enough, the table with such an index but without a primary key should not be accepted. unique index with NOT NULL columns is as good a a primary key.

Anyway, this task is about adding HA_REQUIRE_PRIMARY_KEY flag, there's no need to do anything beyond that (and tests, of course).

revno: 3984
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 10.1
timestamp: Tue 2014-03-11 13:49:52 +0200
message:
Added multi-key unique test case.
------------------------------------------------------------
revno: 3983
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 10.1
timestamp: Tue 2014-03-11 13:40:29 +0200
message:
MDEV-5335: Force PK option. Added a new dynamic configuration variable
innodb_force_primary_key default off. If option is true, create table without
primary key or unique key where all keyparts are NOT NULL is not
accepted. Instead an error message is printed. Variable value can
be changed with set global innodb_force_primary_key = <value>.

Setting option on by default is not a option, too many existing tests would fail.