PHP/MySQL Delete Row...Automatic Shift Numbers Down?

Hello all!
I'll make it short and sweet...

I have a database, it looks something like this:

id data
--- -------------------------
0 Some Data
1 Some Other Data
2 Some More Other Data


Note:
id = INT NOT NULL PRIMARY KEY
data = TEXT NOT NULL

Now, I:
DELETE FROM whatever WHERE id='1';

Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again? I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

Thanks!
- Merlin

Merlin wrote:

> Then the ID numbers go "0, 2".**Is*there*a*way*to*automatically*shift*all
> the ID numbers, after the one deleted, to go in order again?**I*can*write
> a script to update every row, I just want to know if there is a PHP/MySQL
> function that automatically does it.

I'm not quite sure exactly why you'd want to do this but you'd need to do it
manually like so:

DELETE FROM whatever WHERE id = X ;
UPDATE whatever SET id = id - 1 WHERE id > X;

and subsitute X for the number.

Note that if you're referring to this id number in other tables you are
going to have a much more difficult time as you also need to update allthe
values in all of those tables as well.

Unless you have a specific need to do so, is there really any reason todo
this? If there isn't, you'd probably be better off using an auto
incrementing primary key so you don't need to work out what the id value
should be each time you do an insert.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

ALTER TABLE whatever AUTO_INCREMENT=0 would work just as well.

On Sat, 12 Jun 2004 12:20:06 +1200, Chris Hope
<chris@electrictoolbox.com> wrote:

>Merlin wrote:
>
>> Then the ID numbers go "0, 2".**Is*there*a*way*to*automatically*shift*all
>> the ID numbers, after the one deleted, to go in order again?**I*can*write
>> a script to update every row, I just want to know if there is a PHP/MySQL
>> function that automatically does it.
>
>I'm not quite sure exactly why you'd want to do this but you'd need to do it
>manually like so:
>
> DELETE FROM whatever WHERE id = X ;
> UPDATE whatever SET id = id - 1 WHERE id > X;
>
>and subsitute X for the number.
>
>Note that if you're referring to this id number in other tables you are
>going to have a much more difficult time as you also need to update all the
>values in all of those tables as well.
>
>Unless you have a specific need to do so, is there really any reason to do
>this? If there isn't, you'd probably be better off using an auto
>incrementing primary key so you don't need to work out what the id value
>should be each time you do an insert.

Merlin wrote:

> id = INT NOT NULL PRIMARY KEY
> data = TEXT NOT NULL
[...]
> Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
> the ID numbers, after the one deleted, to go in order again? I can write a
> script to update every row, I just want to know if there is a PHP/MySQL
> function that automatically does it.

I am sure you do not want to take care of id's by yourself. Normally
id's are created as AUTO_INCREMENT:

my_id BIGINT NOT NULL AUTO_INCREMENT

Now MySQL takes care about this. Adding a new item to the db increases
my_id by 1.

Rgds,
Frank

--
http://landseer-stuttgart.de

On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:

>I'll make it short and sweet...
>
>I have a database, it looks something like this:
>
>id data
>--- -------------------------
>0 Some Data
>1 Some Other Data
>2 Some More Other Data
>
>
>Note:
>id = INT NOT NULL PRIMARY KEY
>data = TEXT NOT NULL
>
>Now, I:
>DELETE FROM whatever WHERE id='1';
>
>Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
>the ID numbers, after the one deleted, to go in order again? I can write a
>script to update every row, I just want to know if there is a PHP/MySQL
>function that automatically does it.

Automatically? No, since MySQL doesn't support triggers or similar constructs.
Besides, why would you want to anyway? Deleting a row shouldn't change the
identity of other rows.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

In message <ud4mc09101cak5rddb6c8cckm8akp7ns6t@4ax.com>, Andy Hassall
<andy@andyh.co.uk> writes
>On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:
>
>>I'll make it short and sweet...
>>
>>I have a database, it looks something like this:
>>
>>id data
>>--- -------------------------
>>0 Some Data
>>1 Some Other Data
>>2 Some More Other Data
>>
>>
>>Note:
>>id = INT NOT NULL PRIMARY KEY
>>data = TEXT NOT NULL
>>
>>Now, I:
>>DELETE FROM whatever WHERE id='1';
>>
>>Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
>>the ID numbers, after the one deleted, to go in order again?

They will still go in order - it's just there will be a gap or two.

>> I can write a
>>script to update every row, I just want to know if there is a PHP/MySQL
>>function that automatically does it.
>
> Automatically? No, since MySQL doesn't support triggers or similar constructs.
>Besides, why would you want to anyway? Deleting a row shouldn't change the
>identity of other rows.

Some database designs use triggers to delete rows in other tables using
a deleted key as a foreign key - they need to be darn sure they really
want to delete it...

--
Five Cats
Email to: cats_spam at uk2 dot net

I noticed that Message-ID:
<9Qryc.2117$Wr.1705@newsread1.news.pas.earthlink.n et> from Merlin
contained the following:

>Now, I:
>DELETE FROM whatever WHERE id='1';
>
>Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
>the ID numbers, after the one deleted, to go in order again? I can write a
>script to update every row, I just want to know if there is a PHP/MySQL
>function that automatically does it.

The ID ( or primary key) simply has to be unique. It doesn't have to be
in order, it doesn't even have to be a number, any random unique string
will do. Furthermore, it should not change

Any order in the results from a database is specified by you, as it
should be. For instance if you wish to sort the results in the date they
were entered you should incorporate a field including a timestamp and
order the results on that.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/