[SOLVED] Primary ID field still continuing despite row deletion

[Someone789]

Hi,

 

I'm having an issue with the numeric ordering of my 'id' field in a table after I deleted a row. Basically, I need the primary key 'id' field in my table to reflect the actual number of rows in my table, even after a row is deleted.

 

For example, I have a table like this in my database:

 

id | color

1  | blue

2  | red

3  | green

4  | yellow

 

I no longer wanted "yellow" to be in there, so I dropped the entire 4th row. I then needed to insert "purple" and did so, but it came out like this:

 

id | color

1  | blue

2  | red

3  | green

5  | purple

 

While what I wanted was this:

 

id | color

1  | blue

2  | red

3  | green

4  | purple

How can I make it so that new insertions don't skip over the number of the row that was previously dropped?

 

The "id" field was first created using these properties if that helps: INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)

 

Many thanks in advance!

 

 

 

 

 

[Someone789]

Never mind, found the answer:

 

mysql_query("ALTER TABLE colors AUTO_INCREMENT = 4")

or die(mysql_error()); 

[fenway]

BAD IDEA.

[corbin]

To elaborate on fenway's post, you shouldn't reuse a primary ID.  What if you have data depending on it or something?

[fenway]

To elaborate on fenway's post, you shouldn't reuse a primary ID.  What if you have data depending on it or something?

Thanks for reading between the lines.

 

I just get tired of having to deal with some of mysql's more annoying features -- like leaving off ONLY_FULL_GROUP_BY in the sql mode, allowing resetting of the auto-increment counter without truncate, etc.