mysql primary key question

[willg825]

Hi all--

This seems like a pretty simple problem (and I'm not sure if its even a problem.)

I have a field that is defined as follows:

CREATE TABLE mytable (
org_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
etc.etc.etc.
)
type=MyISAM

I use phpMyAdmin to visualize the database, but when I delete a row, the rest of the rows dont change their org_id (the primary key) - they stay the same. When I add a new row, it automatically increases, but if I delete a row in the middle, none of the numbers change. IS this the way primary keys are supposed to work? I've been googling this for a while now and I cant seem to find an answer. Is there a way to create a primary key that will always be appropriately numbered?

Thanks!

will

[caspita]

That is the way it is suppose to work, if you want to reuse deleted numbers you have to do it on your own and create a regular index. Autoincrement is not more that add 1 to the highest number in the column.

CS.

[willg825]

So create a regular index, and then run some sort of script to change all of the other IDs whenever something is edited... I guess thats what I'll have to do. Thanks!

-will

[ergophobe]

Will,

I do that on RARE occasions. For example, I want to store the last ten searches for an individual, and a given individual might make hundreds of searches a day. Since I will only be holding 10/user at any time, I only need say a 4-digit number for the search ids. Thus, I just reuse the same ten numbers for a given user.

So I'm doing it just to avoid ending up with 12 digit search ids after a couple of years when the table only needs to hold say 100 records.

I'm not doing it because I want the sequence filled or the numbers to be sequential. If you are using related tables (which normally you should be), renumbering the primary key is going to create total havoc with the relationships unless you are very careful.

Tom