Modifications to an existing table

[WarKirby]

Hi everyone. I'm very new to mysql and php, learning as I go. Working on a database which has a problematic design oversight. The primary key is a combination of two fields, on the assumption that these fields would never be the same, but that is not the case as I find the need to create multiple records with identical values in these fields

 

So I want to alter this table, to add a new AUTO INCREMENT column, and change the primary key to that.  The table currently has just under 10,000 records in it which are valuable, so I can't afford to mess this up. Can anyone advise as to what I should do? I'd assume the ALTER query will be needed.

 

Thank you in advance for replies

[WarKirby]

My mysql version is 5.0.67-log source distribution

Apologies for the double post, was going to edit this into the OP but it seems I can't now.

[Mchl]

1st rule of database tinkering. Never work on live database. Do all your tests on separate database.

This way there is no possibility of you messing anything up.

 

Perhaps the easiest way is to:

1. Create new table with AUTO_INCREMENT column in it and all other fields from original table.

2. Use INSERT INTO ... SELECT to fill the new table with values (and automatically generate AUTO_INCREMENT values)

3. Drop the original table

4. Rename your new table, to what it is supposed to be called

[WarKirby]

1st rule of database tinkering. Never work on live database. Do all your tests on separate database.

This way there is no possibility of you messing anything up.

 

I was planning to do all tests and stuff on a backup of the live database first, and then run it on the live one when I'm sure everything is fine. That a bad idea?

 

Perhaps the easiest way is to:

1. Create new table with AUTO_INCREMENT column in it and all other fields from original table.

2. Use INSERT INTO ... SELECT to fill the new table with values (and automatically generate AUTO_INCREMENT values)

3. Drop the original table

4. Rename your new table, to what it is supposed to be called

 

Does this mean the auto increment column won't auto fill itself when it's added? I was hoping it would do that.

 

So if I understand right, I just run a loop that copies all the data one row at a time from the old table, to the new? (ordered by date of course) And I don't put anything into the auto increment column, because it will auto fill itself as each row is added?

 

[Mchl]

 

I was planning to do all tests and stuff on a backup of the live database first, and then run it on the live one when I'm sure everything is fine. That a bad idea?

 

 

Let's straighten a few things:

1. Production database - that's what's being used by your application/website everyday. You really do not want to screw this up.

2. Backup - you do (you really should anyway) do regular copies of your production database, so that you minimise the risk of loosing your data

3. Develoment database - that's something you preferably set up on your own PC, so that you can do all sorts of experiments with it, without worrying about doing something wrong.

 

Does this mean the auto increment column won't auto fill itself when it's added? I was hoping it would do that.

 

As far as I remember it won't. But I might be wrong. You can always try

 

So if I understand right, I just run a loop that copies all the data one row at a time from the old table, to the new? (ordered by date of course) And I don't put anything into the auto increment column, because it will auto fill itself as each row is added?

 

No. By using somethin like

INSERT INTO newTable (field1,field2,field3) SELECT field1,field2,field3 FROM oldTable

you virtually copy all contents from oldTable to newTable on one go.

[WarKirby]

INSERT INTO newTable (field1,field2,field3) SELECT field1,field2,field3 FROM oldTable

 

 

Oh, neat. And just to be clear, that will populate the auto increment field in the new table appropriately, yes ?

[Mchl]

Yes (as long as you remember to make it AUTO_INCREMENT ). You can do usual sorting by ORDER BY in the SELECT statement.

[WarKirby]

Agreed. I've written and run a test script doing pretty much that, and so far it's perfect. Finished in about 10 seconds (including ~9700 echo calls).

 

Not run anything on my live databse yet, but I'm confident that this part at least will go smoothly when I do