Re: Mysql autoincrement problem

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.

Comment on Re: Mysql autoincrement problem

Replies are listed 'Best First'.
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]