AUTO_INCREMENT Question

Reamer Fanny

Here is the situation, I have a customer database where I can add & remove data, CustID acts as the primary key and is set to AUTO_INCREMENT when I add a new customer it increases, but when I delete a customer it will continue to step up. How would I change each CustID, once one is deleted?

CustID Name
1 John <-- Next query deletes John and adds Steve.

CustID Name
2 Steve <-- Steve should take Johns place and have CustID = 1 instead of counting up.

Does this make any sense?

fasty

That's not how it works. You would then need to go through every table that uses the CustID as a foreign key and update that too or your data would turn to muck.

The idea behind using an autoincrementing integer as a key is that it's unique, Does it really matter what it is?

Reamer Fanny

fasty wrote: »

That's not how it works. You would then need to go through every table that uses the CustID as a foreign key and update that too or your data would turn to muck.

The idea behind using an autoincrementing integer as a key is that it's unique, Does it really matter what it is?

No I suppose you are right

mark renton

Thats correct - really, data of a customer header nature should not be deleted, it can be archived if no longer required