I have three tables, Customers, Address and CustomerAddress where CustomerAddress contains two foreign keys to link Customers and Addresses in a many to many relationship. I would like to generate some columns in the Customer table, (ie Name) and then use the relationship from the CustomerAddress table to populate the corresponding Name field in the address table (it is the same field in both tables). I am encountering the following errors
1. In Customers, when I generate the Name field and delete existing data I get an error on the Foreign Keys.
2. In Customers if I generate the Name field and do not delete existing data I get an error saying '.. CustomerID, cannot generate any more values" .
3. I have not been able to test using an SQL statement to copy the generated name fields from Customers to Addresses due to the first two errors.
I have been reading the forums and see some instructions to delete the table(s) with the foreign keys. Generate the new data and then recreate the tables. However, I would like to preserve the foreign key relationships from the original data and I can't see how to do this if I delete the table. I am a bit rusty on my SQL so perhaps I am missing something, or perhaps there is a better approach to this problem? Any advice or tutorials would be most useful.