Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Mar 31 '11 at 2:06

This question came from our site for professional system and network administrators.

add comment

3 Answers 3

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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,.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.