Is it always a good idea not to have the primary key id for the table?

Question

It seems to me that this is always a good idea, but is there ever a case when you better not have this in the table?

Answer 1

In my experience, almost never. (For “speed matters, I just insert and am not really interested in searching at that point,” for example, the style of the application.)

Although you may never use the ID field, it is almost always wise for one of them to be AUTO_INCREMENTing happy, because one day you might need one. (You could just do "ALTER .." to add one, but that's beyond the point.)

Answer 2

As a rule, you should have some kind of PRIMARY KEY .

There are really really special situations when you do not want, first of all, heap-organized (non-clustered) log tables that do not have indexes to speed up inserts.

Note that in MySQL , InnoDB tables cannot be heaped, so this only applies to MyISAM tables.

Also note that a PRIMARY KEY can be composite, as in the many-to-many relationship table. You will not have an id column in such a table, but you will have a composite primary key consisting of id columns of related tables.

See this question for more information:

• If each table has a primary key?

Answer 3

The short answer is yes.

The answer is longer: if you have a table that will be used in a many-to-many relationship, you really don't need a primary key. Then it can be regarded as a waste of space. There may be more examples, this is just one proof of the yes answer :-)

Answer 4

Having a primary key is a good idea (and necessary if you want to completely normalize the database structure).

Personally, if the table has a natural candidate key, I will use this most of the time, instead of adding an identifier column that must be artificially filled.

Answer 5

For databases, the first normal form or the second normal form .

Answer 6

For performance / space, I avoid autonomer identifier fields if possible. If you have a very large table (many millions or billions of records), then space is important. if you can find a good, useful primary key based on another field (or fields), then you'd better use this than enter an identifier field.

It makes no sense to have a primary key with an automatic number, if you can use a set of fields, you have a unique index as the primary key. You just need to be careful with UPDATE to maintain referential integrity.

Answer 7

It depends on your data design and the way you access the data in the table.

Typically, the log table does not need a primary key, because you often access the logs in groups and do not delete / edit individual messages (you can clear the entire table or log time window).

In other cases, the identifier field may be redundant. If customers subscribe to your site using OpenID (or just an email address), you already have your primary key. However, in this case, it is recommended to add an excess ID field, because the integer uses less space than the lines, and you must repeat the identifier in all respects in which the entity participates!

Finally, relationship tables do not need an explicit identifier in 99% of cases. Example: Many-to-many relationship between users and groups. The relationship table consists only of the user ID and the group ID , which is your main key (you would like to index two fields separately for performance ...).

By the way, an auto-incrementing identifier does not slow performance. The counter value is stored in the table metadata, so when the DBMS inserts, it automatically increases the reference counter, and this can be done using the equivalent Java AtomicInteger and C # Interlocked , so you don’t have to worry about it at all!