Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Question

Is it possible to create an InnoDB table without a Primary Key?

Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that table still be a clustered index, but around a secondary key?

Thanks.

Answer 1

If you have a unique key that is not null, it gets promoted internally to be the clustered key (or if you have more than one that matches this criteria - the first one does.)

If you don't have a unique key that is not null, a hidden key is created that is 6 bytes in length.

There is no way to specify the key to cluster around, and there is no way to not have a clustered index.

Answer 2

Yes, on any RDBMS, if you still have a unique index with non-nullable columns to identify individual rows.

The difference between a "unique key" and a "primary key" is a PK does not allow NULLs. NULL does not compare to NULL so you can not uniquely identify a row with a nullable unique key. Especially if your RDBMS ignores NULLs for uniqueness (e.g. SQL Server allows at most one NULL in a unique index)

Any of the indexes (A PK is an index) can be clustered: but every table should have clustered index (outside of staging table that are cleared after use). The clustered index can be the PK, the unique key, or another index.

I'm sure MySQL allows separation of index uniqueness and clustering

Answer 3

InnoDB will first test to see if there is a PRIMARY KEY, if there isn't one, it will check for a UNIQUE KEY and use it for clustering the data, if there is no UNIQUE KEY, then InnoDB will generate an internal 6-byte integer key and use it as the PRIMARY KEY,.