Home > Archive > PHP SQL > August 2004 > Updating auto_increment fields ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Updating auto_increment fields ?
|
|
| Pjotr Wedersteers 2004-08-30, 8:57 am |
| I have a table with quiz questions. Each question has a unique ID, based on
an auto_increment field.
Now I've discovered that with deleting rows from the table the deleted ID's
are not reissued. Makes sense, just thought of that a bit late.
How can I simply re-index or update or how one would call it, the columns
holding the ID numbers ? Is there a ready-made mysql command or do I have to
load all rows in PHP and modify where necessary ?
TIA
Pjotr
| |
| Alvaro G Vicario 2004-08-30, 8:57 am |
| *** Pjotr Wedersteers wrote/escribió (Mon, 30 Aug 2004 10:47:43 +0200):
> I have a table with quiz questions. Each question has a unique ID, based on
> an auto_increment field.
> Now I've discovered that with deleting rows from the table the deleted ID's
> are not reissued. Makes sense, just thought of that a bit late.
>
> How can I simply re-index or update or how one would call it, the columns
> holding the ID numbers ? Is there a ready-made mysql command or do I have to
> load all rows in PHP and modify where necessary ?
I wouldn't play with that. If auto-generated IDs are not valid for you,
just quite using them. You can make your column UNIQUE to prevent
duplicates and assign your own IDs from the very beginning.
--
-- Álvaro G. Vicario - Burgos, Spain
-- Questions sent to my mailbox will be billed ;-)
--
| |
| Tony Marston 2004-08-30, 8:57 am |
| There is no automatic way to rebuild a table and have the missing
auto-increment values used up. This could cause a serious problem if one of
these primary keys was a foreign key on another table.
Why do you need a deleted row to be re-used? What problem does it cause to
have a missing id number?
--
Tony Marston
http://www.tonymarston.net
"Pjotr Wedersteers" <x33159@westerterp.com> wrote in message
news:4132e9af$0$65124$e4fe514c@news.xs4all.nl...
>I have a table with quiz questions. Each question has a unique ID, based on
> an auto_increment field.
> Now I've discovered that with deleting rows from the table the deleted
> ID's
> are not reissued. Makes sense, just thought of that a bit late.
>
> How can I simply re-index or update or how one would call it, the columns
> holding the ID numbers ? Is there a ready-made mysql command or do I have
> to
> load all rows in PHP and modify where necessary ?
>
> TIA
> Pjotr
>
>
| |
| Pjotr Wedersteers 2004-08-30, 8:57 am |
| Tony Marston wrote:[color=darkred]
> There is no automatic way to rebuild a table and have the missing
> auto-increment values used up. This could cause a serious problem if
> one of these primary keys was a foreign key on another table.
>
> Why do you need a deleted row to be re-used? What problem does it
> cause to have a missing id number?
>
>
> "Pjotr Wedersteers" <x33159@westerterp.com> wrote in message
> news:4132e9af$0$65124$e4fe514c@news.xs4all.nl...
I intended on using a random ID number 1..num_rows and retrieving the
question with that ID from the table. I will now have to do an additional
check to see whether mysql_reult for that random number holds a row of data
indeed. No biggy in this case. I think in retrospect in this case
auto_increment just wasn't the field to use. I had better used a UNIQUE id
field I numbered myself.
Thanks both for the advise. I somehow already figured tampering with
auto-increment data wasn't a wise plan.
Pjotr
|
|
|
|
|