|
|
Posted by Erwin Moller on 04/27/06 11:08
cover wrote:
> After a while of deleting records in a MySQL db, there gets to be the
> gaps in the id numbering system. i.e. 1, 2, 3, 6, 7, 12, and so on.
> Is there a way to renumber the id system in a table for 1, 2, 3, 4, 5,
> 6, etc without manually going in and changing those numbers?
I hope not.
Why do you want such a strange thing?
While designing a database, good designers make use of foreign keys to to
make sure the relations in the database will stay consistent.
Consider the following pseudocode:
create table tbluser(
userid autonumber Primary Key,
username text
);
create table writtenarticles(
writtenarticleid autonumber Primary Key,
writtenby numeric references tbluser(userid),
title text,
article text
);
The above situation makes sure that the value for writtenby in
tblwrittenarticles exists in column userid in table userid.
If you decide to change the numbers in tbluser, this relation will be broken
(and fires a exception/error in most cases.)
However, in some databases you have the option to cascade a change through
all related tables via their foreign keys.
Bottomline: Such an autonumber/serial field is ment to uniquely point to a
certain row, which is handy in a relational database.
It is not ment as a counter...
If you need such a counter, just do it programmatically, like looping over
your resultset (ordered by userid eg), and increase your own counter.
I don't want to lecture you, but I think what you asking for is conceptually
wrong. :-)
Good luck.
Regards,
Erwin Moller
Navigation:
[Reply to this message]
|