469,925 Members | 1,510 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,925 developers. It's quick & easy.

Updating auto_increment fields ?

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
Jul 17 '05 #1
4 1823
*** 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 ;-)
--
Jul 17 '05 #2
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" <x3****@westerterp.com> wrote in message
news:41***********************@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

Jul 17 '05 #3
Tony Marston wrote:
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" <x3****@westerterp.com> wrote in message
news:41***********************@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


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
Jul 17 '05 #4
In article <41*********************@news.xs4all.nl>, Pjotr Wedersteers wrote:
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.


If you are using MySQL:

SELECT * FROM table ORDER BY RAND();

--
Tim Van Wassenhove <http://home.mysth.be/~timvw>
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.