WarKirby Posted December 28, 2009 Share Posted December 28, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/ Share on other sites More sharing options...
WarKirby Posted December 28, 2009 Author Share Posted December 28, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985075 Share on other sites More sharing options...
Mchl Posted December 28, 2009 Share Posted December 28, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985081 Share on other sites More sharing options...
WarKirby Posted December 28, 2009 Author Share Posted December 28, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985117 Share on other sites More sharing options...
Mchl Posted December 28, 2009 Share Posted December 28, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985124 Share on other sites More sharing options...
WarKirby Posted December 28, 2009 Author Share Posted December 28, 2009 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 ? Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985127 Share on other sites More sharing options...
Mchl Posted December 28, 2009 Share Posted December 28, 2009 Yes (as long as you remember to make it AUTO_INCREMENT ). You can do usual sorting by ORDER BY in the SELECT statement. Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985129 Share on other sites More sharing options...
WarKirby Posted December 30, 2009 Author Share Posted December 30, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/186521-modifications-to-an-existing-table/#findComment-985822 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.