Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts

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

    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?

  • #2
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    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

    Edit:
    If you would like to pick the brain of a genuine database guru, maybe tap r937 over at sitepoint on the shoulder.
    Last edited by bullant; 06-01-2011 at 02:54 AM.

  • Users who have thanked bullant for this post:

    skcin7 (06-01-2011)

  • #3
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • #4
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    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.

  • #5
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    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

  • #6
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •