View Full Version : Is it bad for any reason to DELETE a SQL entry and leave an auto-incrementing ID gap?

06-01-2011, 03:18 AM
Is it bad for any reason to DELETE an entry from a SQL database where you have an auto-incrementing primary key, which would then result in gaps in the entries?

For example I have a database full of messages. A user MUST be able to delete messages from his inbox if he wants. So he deletes it. It's removed from his inbox. Is it better to FULLY delete that entry from the database, or create a separate boolean field which denotes if an entry has been deleted or not, and only output the non-deleted ones?

Is there anything you have to watch out for when choosing to fully delete an entry from a database that has an auto-incrementing key? The main thing I'm concerned about is gaps in the sequence. e.g. 5, 6, 7, 9, 12, 14, etc. because if you allow entries to be FULLY deleted the sequence would be off.

For tables that have auto-incrementing primary keys, when it comes to deleting, do you guys fully delete them from the database, or add a separate boolean field to denote if it is deleted or not?

06-01-2011, 03:31 AM
You can do either - physically remove a record from the table or just set a flag in the row saying to not display it or not return the row in an sql query. Which you do depends on the circumstances.

For example, bulletin boards will often, if not always, simply set a flag in a database saying a user has deleted a message without actually deleting it from the db table. This way, moderators can still see the contents of a post "deleted" by a user for some reason.

On the other hand, when I delete, via a form, a product from my products db table, it is physically removed from the table. Having gaps in auto-incrementing primary keys does not affect anything afaik.

For example:

If i initially have product id's (auto-incrementing primary key) of 1,2,3,4,5,6,7 and then delete product 6 and then add a new product, the resulting product id's in the table will be 1,2,3,4,5,7,8

If you would like to pick the brain of a genuine database guru, maybe tap r937 over at sitepoint on the shoulder.

06-01-2011, 04:05 AM
The only purpose of an auto incrementing field is to give each row a unique id. Doesn't matter a monkeys whether you delete the row, retain it and flag it as unavailable or whatever. That choice depends on your data retention preference alone. The database will still increment for each new entry regardless. The primary key is, to all intent and purpose, a reference point and nothing more.

06-01-2011, 06:25 AM
Thank you for your replies. The reason why I thought non-sequential primary keys might be a bad idea is in case for whatever reason you need to search all the entries in the database somehow sequentially by its ID like for a search algorithm or something, a gap could screw it up. I think I am nit-picking at this point. Either way, thank you.

06-01-2011, 06:30 AM
no problem :)

having gaps in the primary key values would cause problems only if for some reason no gaps were allowed. Off the top of my head I can't think of a "real world" situation:confused:

06-01-2011, 09:13 AM
Ok cool. Yeah like I said I think I was being anal about it haha. But then again for whatever reason I am a perfectionist when I make stuff. This is actually a bad thing because I have been known to spend WAY too much time on the simplest and dumbest of stuff that nobody cares about and waste time but anywho yea thanks for the help yo!