Hi guys,
I have a table which is dynamically updated. It has an id column which is primary_key and auto_increment. When a row is deleted and a new is created the new id is as it should have been if the previous had not been deleted. I do not want this to be in this way but when I delete for example number eight and a new is created(After I have deleted number eight) to have my new one numbered eight not nine.
I can do this without id being auto_increment but I have to do it in the way as described above.
Auto increment question
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Why does the id have to be without gaps? This is not the intended purpose of auto_increment. If your wanting to number the rows, do that in the outputting.
Code: Select all
$i = 0;
while ($row = mysql_fetch_assoc($result))
{
$i++;
echo $i. ' ' $row['foobar'];
}Reply
I do know this but I need it in the way I said above. I have just given an example with a table. I have about one hundred table and most of them are related to each other by this id but if some of them change(update, delete or insert) others do not(I know this is not as most systems work but the system I am creating is a little strange.). So when I have some of my ids deleted and some not I get incorrect results.
BTW:Thank you for your help.
BTW:Thank you for your help.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
There's absolutely no need for a column like this...
But if you have to, remove the auto increment from the field and just put a 1 in the primary key of the first row in the table, then whenever you use INSERT, use a SELECT query to fetch the highest primary key and then add 1 to it and use it as the new primary key in the INSERT query.
You could probably do this with a subquery.
But if you have to, remove the auto increment from the field and just put a 1 in the primary key of the first row in the table, then whenever you use INSERT, use a SELECT query to fetch the highest primary key and then add 1 to it and use it as the new primary key in the INSERT query.
You could probably do this with a subquery.
Re: Reply
It sounds to me that you want to look into referrential constraints, more specifically: foreign key constraints... Since you mentionned auto_increment i presume that you're using mysql: http://dev.mysql.com/doc/refman/5.0/en/ ... aints.htmluser___ wrote:I do know this but I need it in the way I said above. I have just given an example with a table. I have about one hundred table and most of them are related to each other by this id but if some of them change(update, delete or insert) others do not(I know this is not as most systems work but the system I am creating is a little strange.). So when I have some of my ids deleted and some not I get incorrect results.