mysql auto increment primary key running out

I maintain a table with an ID AUTO INCREMENT PRIMARY KEY. When I de开发者_StackOverflow社区lete an entry and re-add one, the new entry does not take the ID of the previous one instead it increments again by one. Is that normal and is it advised to not change this behavior? I just have a feeling this is creating a non scalable system as eventually it could run out of indexes.

This is by design, million of databases have primary keys like these with an integer key.

If you delete 90% of your inserts, you will run out of keys after 400 million rows¹⁾
If and when you do you can do an

ALTER TABLE `test`.`table1` MODIFY COLUMN `item_id` BIGINT UNSIGNED NOT NULL
, ROW_FORMAT = DYNAMIC;

where column item_id would be your primary key.
After that you'll never have to worry about running out of key-space again.

Don't be tempted to start out with a bigint primary key!

1. It will make all your queries slower.
2. It will make the tables bigger.
3. On InnoDB the primary key is included on every secondary index, making a small primary key much faster with inserts.
4. For most tables you will never need it.

If you know your big table will have more rows than the integer can hold, than by all means make it a bigint, but you should only do this for tables that really need it. Especially on InnoDB tables.

Don't use a GUID, it's just a lot of wasted space, slowing everything way down for no reason 99,99% of the time.

¹⁾ using a unsigned! integer as primary key.

User niceguy07 uploads a picture of his kitten. The picture is saved as 000012334.jpg because you use primary keys as filenames instead of putting untrusted user data into them (which is a good idea).

niceguy07 sends a link with ?picture_id=12334 to his date.

niceguy07 deletes his kitten pictures and user fatperv08 uploads a picture of himself wearing only a batman mask.

Your database reuses primary keys, so unfortulately now the link with ?picture_id=12334 points to a picture of a naked fat perv wearing a batman mask.

Re-using primary key values of deleted records is an extremely bad idea. It is, in fact, a bug, if the primary key leaks out of the database because you use it in :

• a URL
• a filename
• dumped along with other data in a file
• etc

Since it is, in fact, very useful to do all of the above, not reusing primary key ids is a good idea...

It's fine. Depending on how many records you expect, you may want to make sure it's a bigint type, but int should be fine in most cases.

It is normal. Don't worry about ID's being sequential.

It depends on the number of records you plan on having and how often they are deleted and added, but if it's going to be an issue, use a bigint primary key.

There are also other options, such as using a GUID, if you are truely worried about running out of rows, but I've never run into a situation where I actually needed a bigint, I just occasionally use them on volitle tables to be safe.

A primary key in database design should be seen as a unique identifier of the info being represented. By removing an ID from a table you are essentially saying that this record shall be no more. If something was to take its place you are saying that the record was brought back to life. Now technically when you removed it in the first place you should have removed all foreign key references as well. One may be tempted at this point to say well since all traces are gone than there is no reason something shouldn't take it's place. Well, what about backups? Lets say you removed the record by accident, and it ended up replaced by something else, you would not be able to easily restore that record. This could also potentially cause problems with rollbacks. So not only is reusing ID's not necessary but fundamentally wrong in the theory of database design.

adding to dykstrad's great comment

If for example the primary key points to a point of sale price as an example, say a diet coke. Sloppy house keeping you delete the old price and reinsert the new price. Good housekeeping would dictate that you would do an update on the item which preserves the key / referential relationships as it is still the same item / unique relationship