surajkandukuri Posted November 20, 2009 Share Posted November 20, 2009 Hi, I have table sample with 2 columns IngID and Employee Name with IngID being Auto increment value and primary key I had 3 records in my database, After I delete 2 records and try to add a new record IngID becomes 4 which should be 2 (as records 2 and 3 are deleted) What could be the problem?? and The solution for this Quote Link to comment Share on other sites More sharing options...
monkeytooth Posted November 20, 2009 Share Posted November 20, 2009 Auto Increment only goes one way, up. Deleting a row will not change the value in a downward manor. Also I think once a database is live and for public consumption its a bad idea to delete any rows. But that's me. It only goes up cause think about when you have more rows. If you have 100 rows and delete rows 2-9 and 45, 63, 72 each row already has an auto increment value. If the value goes down to match the row count then you are either going to end up with doubles of already existing id's or you are going to end up causing the db to do more work then it should by not only matching the row count but making it go down the list changing each variable to match it. Then you get into other dynamics that are just to much to start typing about. To many x factors so to speak that can break ones system if they are developing a dynamic site. If everything's static then I can understand I guess. Quote Link to comment Share on other sites More sharing options...
monkeytooth Posted November 20, 2009 Share Posted November 20, 2009 Just want to append to that.. ID's they are all unique for a reason. Grant it you are not enforcing the Unique option with the DB for the columns from what I am assuming. But just wanted to mention it regardless.. Quote Link to comment Share on other sites More sharing options...
Zane Posted November 20, 2009 Share Posted November 20, 2009 That's the way it's supposed to be. Otherwise you would have to update the ids/links/html/etc for all of your products every time you deleted one. Products are put into a database with a primary key so that they have just that. A PRIMARY KEY. One number in particular that identifies it for as long as it is in the database. True, once you delete id's you maybe have endless phantom numbers out there, but hey.. it's like having a spare tire or something. You can always query a database of any size to find the missing numbers.. It won't hurt the integrity of your database whatsoever.. at least I wouldn't think it would. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 20, 2009 Share Posted November 20, 2009 And you would need quite some time to use all IDs that INTEGER datatype can hold (4 billion+). It bothered me too at my beginnings with databases, but then I started to think along these lines: these values are for the machine, not humans, and machines don't care if something looks nice or not. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 25, 2009 Share Posted November 25, 2009 Sounds like you want to "number" your records, which is up to your application code to handle. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.