Mysql auto increment primary key id’s

auto-incrementMySQLprimary-key

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?

Best Answer

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!).

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – Reorder / reset auto increment primary key

Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADE instead of the default ON UPDATE NO ACTION for the foreign key relationship in those tables.

Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.

ALTER TABLE `users` AUTO_INCREMENT = 1;

For MySQLs it will reset the value to MAX(id) + 1.

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – Reorder / reset auto increment primary key

Related Topic