Deleting records and auto increment PK

[liquinas]

Hi, my problem is a simple one yet one that causes much trouble on my application. It's a car inventory database where the owner can add, delete, and remove records.

 

The cars on the database have ID as the primary key. There are 6 cars on the table and the PK is on auto increment so that if I add a new car right now it will be ID#7. The problem is, if I add record #7, and delete it, the auto-increment stays set, so that if I add another record, it will become ID#8 instead of 7 again.

 

Is there anything I can add to the delete query so that the auto increment goes down by 1 each time a record is deleted?

 

Is there any other possible solution to this?

 

Thanks.

[mjlogan]

The usual response to this question is 'it is not advised'. Why do you want the ID to drop, what wrong with having a gap?

[liquinas]

The front page of the site specificaly lists cars with ID 1 through 6.

[mjlogan]

Could you not list the smallest six numbers,

 

say you had cars

 

1,2,5,7,12,13,25,57

 

it would just show

 

1,2,5,7,12,13

 

and to do that in the SQL you would just order by the ID and limit to 6

 

[liquinas]

What would be the "not advised" alternative?

[artacus]

As fenway would say, "You are abusing  your primary keys."

[liquinas]

Yes, I have every intent of pimping my primary keys like Cambodian hookers. And I do think it's quite lame that mysql doesn't automatically reduce it. If this task if possible via any way at all, I'd like to at least know so I can evaluate for myself whether or not it's worth it.

[artacus]

Ok, did I mention that I didn't think this was a good idea?

ALTER TABLE `myTable`  AUTO_INCREMENT =7

[fenway]

Yes, I have every intent of pimping my primary keys like Cambodian hookers. And I do think it's quite lame that mysql doesn't automatically reduce it. If this task if possible via any way at all, I'd like to at least know so I can evaluate for myself whether or not it's worth it.

 

UIDS ARE NOT COUNTERS... don't evaluate it, it's not worth it, nor should it even be allowed.  Order by whatever you want, make this column useful, and if you want to make a counter, do it in PHP.

 

MySQL doesn't do it because it's retarded.

[liquinas]

It seems to work without any nuclear explosions going off, but I might just be talking prematurely:

 

<?php
$user="liquinas";
$host="localhost";
$password="omglol";
$database = "tlcmotors";
$connection = mysql_connect($host,$user,$password)
	or die ("An error occured while accessing the database. Please contact [email protected]");
$DB = mysql_select_db($database,$connection)
	or die ("Error selecting database. Please contact [email protected]");
$query = "DELETE FROM cars WHERE ID='$lol'";
$result = mysql_query($query)
or die ("Problem with Query. Contact [email protected]");
echo "Thingy has been deleted forever and ever<br>";

?>

...

<?php
function getcarinfo()
{
$db = mysql_select_db("tlcmotors")
or die ("Error-Could not select database. Please contact [email protected]");
$query = "SELECT MAX(ID) FROM cars";
$result = mysql_query($query)
or die ("Error-Could not execute query. Please contact [email protected]");
return mysql_result($result,0,0);
}

$lmao= getcarinfo();
$rofl= $lmao-1;
?>

...

<?php
$query = "ALTER TABLE cars AUTO_INCREMENT=$rofl";
$result = mysql_query($query)
or die ("Problem with Query. Contact [email protected]");
echo "Have a nice day.<br>";		
?>

 

It seems to work for all intents and purposes, I'd still like to understand how it isn't a good idea?

[bwochinski]

And how does it handle when someone deletes the car with an ID of 2?

 

Also, your site lists the 6 oldest listings on the front page?  Seems like you'd want the 6 most recent.

[fenway]

There's nothing wrong with that script... because it doesn't play around with the UIDs.