Mysql autoincrement problem

DreamT has asked for the wisdom of the Perl Monks concerning the following question:

(I know this isn't Perl related, but almost:-) ) Let's say i have a 'Customer' table, with autoincrementing 'Customer_ID'. Every new customer becomes 1, 2, 3 etc.

Suddenly, someone adds a numbers of customers with already specified customer numbers, overriding the auto_increment value.

This will, of course, spoil the original auto_increment flag and set it above the highest value (i.e. 1005 or something)

Let's say that I want to keep these values, but I also want to reset the flag to it's original state, so that the customer numbers will continue on 4,5,6 etc.
Is that possible?

Comment on Mysql autoincrement problem

Replies are listed 'Best First'.
Re: Mysql autoincrement problem by lostjimmy (Chaplain) on Aug 18, 2009 at 14:18 UTC
Why would you care what the Customer_ID is? How will it make your application work any better, if after adding a customer with ID 1005, MySQL goes back to 4 for the next ID?	[reply]
Re: Mysql autoincrement problem by JavaFan (Canon) on Aug 18, 2009 at 14:18 UTC
No. The reason is performance. If you want this, you'll have to write the functionality yourself - perhaps by using a trigger.	[reply]
Re: Mysql autoincrement problem by leocharre (Priest) on Aug 18, 2009 at 18:18 UTC
I suggest let autoincrement work as it's supposed to. Make another table for customer ids or another field in the row for this.	[reply]
Re: Mysql autoincrement problem by hangon (Deacon) on Aug 18, 2009 at 20:25 UTC
I agree with leocharre that you're probably better off using a separate column for manually entered customer ID's. However, if there's more to it and you need to write your own routine to handle this problem, take a look at the DBIx::Sequence and DBIx::MySQLSequence modules.	[reply]
Re: Mysql autoincrement problem by proceng (Scribe) on Aug 19, 2009 at 00:26 UTC
Suddenly, someone adds a numbers of customers with already specified customer numbers, overriding the auto_increment value. I'm going to add another wrinkle to this (why you do not want simultaneous autoincremented and externally supplied numbers). You will run into one of two problems: You will have an customer number collision (if unique is not set) You will have an error condition to test for (if unique is set) It is generally a bad idea (from the DBA's standpoint) to mix internal and external sources for an incremented key. Your best bet (if an external customer number is absolutely necessary) is to utilize it as a lookup field for processing but use an autoincrement field (in your primary table) for tying secondary tables together.	[reply]
Re^2: Mysql autoincrement problem by DreamT (Pilgrim) on Aug 19, 2009 at 06:20 UTC
Thank you all for your replies. The reason why we're doing this is because the software has multiple ways of creating customers. The sudden addition of the high customer numbers happened because our system was syncronizing with another customer system, so the customer numbers in that case had to be the same, for different reasons. But it seems like there is no easy way to accomplish this.	[reply]
Re: Mysql autoincrement problem by sanku (Beadle) on Aug 25, 2009 at 05:32 UTC
Hi, you can able to change the customer id auto increment by `alter table <table name> change/modify <column name> AUTO_INCREMENT = +4;` [download] 20090906 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips	[reply] [d/l]