ALTER TABLE will it change my info?

[RussellC]

I have a table that stores customer info. There is a PRIMARY KEY that is called ID there is also a field called jobnumber that is an int. I need to change it to an INT(11) AUTO_INCREMENT field. Now if I change it to auto increment and start it with a number that it past all of the numbers that are in the DB (say start at 5000), will it change any of the numbers that are in there now? Or, from now on, will it just auto inc the number everytime i make a new entry?

Thanks for the help.

[Birdman]

I believe it will automatically start at the next increment of the highest value in the field.

Adding auto-increment to a field should not harm your existing data.

I'm not a db pro, so maybe wait for a more qualified person to come along. I'm almost positive, though.

[RussellC]

I just tried it but I think there can only be one Auto increment number in each table. :(

[Birdman]

Is it MySql?

[RussellC]

yes

[Birdman]

You're right, only one "auto" field allowed.

I would say create a new table for jobnumbers and the you can have a field that would contain the id it pertains to in the original table.

[jatar_k]

Why does it have to be auto increment? You could just do it in your script on insert.

something like "select max(jobnumber) as something from table" and then add one and use it for your insert.

[RussellC]

Thank for the responses guys... I look into both of those options and see what works best...

Thanks again

[bcc1234]

something like "select max(jobnumber) as something from table" and then add one and use it for your insert.

Bad idea. Think of several concurrent transactions which get the same max(jobnumber) and then try to do several inserts.

PostgreSQL has sequences for that purpose, I'm sure MySQL provides something similar. Check the docs and newsgroups.

[jatar_k]

good point bcc1234, a bad example, I was trying to imply more that there are options as opposed to saying this is your answer.

[bcc1234]

I was trying to imply more that there are options as opposed to saying this is your answer

I actually designed my first db-based app that way. It used DB2 running on NT and everything went well during testing.
Boy, was I puzzled when some seemingly random transactions started failing once the system got under load :)

<edit>sp</edit>

[RussellC]

OK looks like the best way for me right now is just to create a separate table and with jobnumber and an id number. I'll get working on it. Thanks.