Mysql auto increment primary key id's

Question

I have some mysql tables that have auto incrementing id's that are primary keys, but I notice that I never actually use them... I used to think that every table must have a primary key so I guess that is why I created them before. Should I remove them all if I don't use them at all?

Answer 1

Unless you are running into space problems I wouldn't remove them.

They are a life saver in case you by mistake (or oversight) populate the database with repeated/wrong data.

They also help to have related tables, where you reference the content on one table through the autogenerated id.

This is assuming you have indexes for the other columns you use to actually query the data (if you don't, then more reason to keep the autoincrement ids and use them!).

Answer 2

Interesting!...

I seem to hold a minority opinion here, getting both upvoted and downvoted to currently an even 0, yet no one in the majority opinion (see responses above) seems to make much of a case for keeping the id field, and the downvoters didn't even bother leaving comments hinting at why doing away with the id is such a bad idea.

In their defense, my own original response did not include any strong argument as to why it is ok to do away with the id attribute in some cases (which seem to apply to the OP). Maybe such a gratuitous response makes it, in of itself, a downvotable response.

Please do educate me, and the OP, by leaving comments pro or against the *systematic* (and I stress "systematic") need to include auto-incremented non-semantic primary keys in all tables. A promised I returned and added to my response to provide a list of reasons why it may be detrimental to [again, systematically] impose a auto-incremented PK.

My original response: You bet! you can remove these!

Before you do anything to the database make sure you have a backup, in particular is the DB size is significant.

Use the ALTER TABLE statement to remove the id in the tables where you want to remove it. Specifically
ALTER TABLE myTable DROP COLUMN id (you also need to remove the PK constraint before removing the id, if the table has such a constraint)

EDIT (Added later)
There are many cases where it just doesn't make sense to carry along an autoincremented ID key, regardless of the relative little extra storage requirement these keys add.
In all these cases, the underlying implication is that

• either the data itself supplies a primary key,
• or,   the application manages the key generation

The key supplied "natively" in the data doesn't necessarily neeeds to be a single column key, it can be a composite key, although in these cases one may wish to study the situation more closely, particularly is the overal key is a bit long.

Here are some of the drawbacks of using an auto-incremeted primary key in lieu of a native or application-supplied key:

• The effective data integrity may go unchecked
  i.e. the server may allow record insertions of updates which create a duplicated [native] key (eventhough the artificial, autoincremented primary key hides this reality)
• When relying on the auto-incremented PK for the support of joins between tables, when part of the [native] key values have to be updated...
  ...we either create the need of deleting the record in full and and re-insert it with the news values,
  ...or the risk of keeping outdated/incorrect links.
• A common "follow-up" with auto-incremented keys is to create a clustered index on the table for this key. This does make sense for tables without an native or application-supplied primary key, so so much for data sets that have such keys. Effectively this prevents choosing a key for the clustered index which may be more beneficial for the most common query patterns.
• Migrating tables with an auto-incremented key can made more difficult depending on the DBMS (need to declare the underlying column as plain integer, prior to copy, then need start again the autoincrement...)
• For narrow tables, i.e. tables with a few columns only, the relative cost of the auto-incremented PK can be significant, and impact performance in a non negligible fashion.
• When inserting new records along with associated records in related tables, the auto-incremented key needs to be obtained after the insertion of the main record, before the related records can be inserted; the logic is simpler when the column values supporting the link are known ahead of time.

To summarize, the idea that so long as the storage can carry the [relatively minimal] extra "weight" of the artificial primary key, we should include and use such a key, is not without drawbacks of its own.

A final consideration is that just like it is rather easy to remove such keys when we don't need them, they too can be easily added, post-facto, when/if it becomes apparent that they are useful in a particular situation. Neither form of refactoring (adding vs. removing the auto-incremented columns) is risk free, but neither is a major production either.

Answer 3

No.

You should keep them; a database always needs something that differentiates a row from another row (a "Key" of some sort).

If you have something that is guaranteed to be unique for each row, then you can use that as a key; otherwise keep the Primary Key and the Auto generated ID.

Answer 4

I'd personally keep them. They will be especially useful at a later date if you expand the database design and need to reference this table.