Page 1 of 1
Equivalent to ID but better
Posted: Fri Jul 07, 2006 11:56 am
by Dave Lohran
So I know the general practice of making the first column in a row an id with 'auto_increment,'
but when you're often removing things from the db, there would inevitably be some gaps in the numbers.
Now, I know that the gaps wouldn't usually cause any problem, but I'd like to know if any of you have run into
problems with the gaps and such, and had to create a solution. My idea was to store the current post number in a file
and then when removing things to decrement that number, and decrement every id after the one that was removed.
I don't know if that's really a problem, but I'd like some confirmation.
I have a news system with a comments system, and the comments are linked to the 'id' variable of the news posts.
Thanks/
Posted: Fri Jul 07, 2006 12:07 pm
by GM
Most DBMS don't allow you to change the primary key of a record, so (assuming that post number was a primary key) your idea is probably not the way to go.
Why are you worried about gaps in the number system? The number of the post is not vital - as long as it's unique it really doesn't matter what number the post is, does it?
Also, if you comments are linked to the id of your news item, you really don't want to be changing the id of the news item, because then you'd need to change all your comments as well!
On solution would be to never delete anything pyhsically from the database, but to set a flag in the record saying that it is deleted. In this way, you never have to remove a record from the table, and so you'll never have holes in the numbering system.
Posted: Fri Jul 07, 2006 12:14 pm
by Dave Lohran
I like that last solution.
Anyway, I have direct access to the sql server with no limitations, so it doesn't matter.
Also, on uniqueness....
Anyway, I have this nasty feeling that eventually, I'm going to do something involving the increment number, or the post numbers or something,
and that this integer isn't going to suffice.
What I'd like to do is create another item in the db that would be incremented independently of SQL's auto_increment.
Looking at it now, I figure there's really not much of a problem, but if I could find a solution for this inexistant problem, that would be nice.
Posted: Fri Jul 07, 2006 12:20 pm
by GM
You can create your own auto_increment solution by creating an integer field, and doing a SELECT MAX(...) before each insert, adding one, and using the result in the INSERT statement.
Personally I've not yet come across a situation where I've not been able to use the standard auto_increment columns though.
EDIT: when I said that the DBMS often doesn't allow you to change the primary key of a record, I meant it was a limitation imposed by the database itself, not by the person who manages the database server. Some databases simply don't let you change it - you have to delete the record, and then recreate it with the new key.
Posted: Fri Jul 07, 2006 12:55 pm
by John Cartwright
Do some research on FOREIGN KEYS

Posted: Fri Jul 07, 2006 12:57 pm
by feyd
Why is it important that the numbers be sequencial?