First off I am enjoying this book. But I have a question about adding foreign keys to an existing table. I completed chapter 8 and I thought I would try mimic the schema for the my_contacts table, I down loaded the example databases. They seem pretty basic and don't match the book exactly, I presume thats so we can build on them?
anyway I ran into an issue after creating the profession table and copying the data into if from the my_contacts table. So now I have a profession table with a Primary key. I then tried to alter the my_contacts table with a new column (prof_id) to be the foreign key for the profession table.
I got an error about not being able to create this new column and key and after digging discovered I could create it if I allowed NULLs. So then realised this error I am getting is because the prof_id column had no entries in it and so trying to create a constraint would not work and only entries that exist in the primary key can be added. (Because with int and not null the column is filled with 0s and the profession table has no 0 index) If I understand constraints correctly?
So as a test I set all the prof_id values to 1 and BOOM...I could create the constraint and foreign key. My question is, is there a way to create the foreign key and have it populate data, from what i have read I would say no but I thought I would ask as I haven't found anything in the book about this. (but that might be because I have to carry on reading)
So the only solution I can think of is to create a query that will match the old profession column from the my_contacts table to the new profession table and insert the primary key values into the prof_id column before I assign the prof_id with a constraint and as the foreign key?