Do MySQL tables need an ID? [duplicate]

Question

Possible Duplicate:
Should each and every table have a primary key?

If you never refer to the ID anywhere is it necessary to include one? Does a table need an ID or primary key?

Answer 1

Actually, InnoDB uses its own row id as PK for the table in case you didn't create one, so it can use it for indexing. And that has some negative effects on performance.

See a very good explanation here: http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/

To sum it up, there are 3 rules:

1. Do explicitly define a primary key, and make it numeric.
2. The primary key should be sequential.
3. The primary key should be as small as possible.

As a side note: some SQL editors and tools may have issues if there is no PK on a table.

When you are manually editing result sets or table data in such a tool, the tool runs an UPDATE command. In case there is no unique key, several identical records may be inserted, and then there is no way to update only one/some of them. In an SQL editor you can manually edit one of those records, but when the update command is sent to the mysql - it will either fail, or update all identical records instead of that one record.