Redefine Relational Tables

[mcfmullen]

Okay, so I messed up big time.

 

I have a database made up of these tables:

Animals - ID, Name, Photo

Themes - ID, Theme, Comment

AnimalsThemes - ID, Name, Theme

 

I had the AnimalsMethods and AnimalsThemes relate Animals.Name to AnimalsThemes.Name and Themes.Theme to AnimalsThemes.Theme

 

Now I realize I should have been relating the IDs.

 

Is there any way I can do this, now that my tables have way too much information to start over?

 

I'm thinking that I should be importing the IDs relating to the names so that I can then delete the names column. Problem is, I don't know how to import the proper IDs to coincide with the proper names.

 

Here's hoping for a solution!

 

 

[fenway]

Are those IDs unique?

[mcfmullen]

Are those IDs unique?

 

Yes. The ID columns are autoincrement primary keys.

[fenway]

Well, then it should be pretty straightforward -- assuming each name has only a single ID, and vice versa.

 

Add new columns called animal_id, theme_id, etc. to the appropriate tables -- these will ultimately replace the name lookups.

 

Then simply issue an update to each table, and use a subquery to pull back the ID for each given name.

 

Then you can drop the other columns -- once you've updated your application code, foreign keys, etc.

[mcfmullen]

 

Then simply issue an update to each table, and use a subquery to pull back the ID for each given name.

 

 

I had assumed the procedure would work similar to your solution but the above is the part I need help with...

[fenway]

update yourTable AS t1 SET t1.newField = ( SELECT ID FROM yourTable WHERE name = t1.name )

[mcfmullen]

Thank you so much!