Working with Foreign Keys
aespaldi
Posts: 2
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.
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.
Comments
Can I first check if the tables in question already have data? And do you want to keep this existing data?
If not, then you don't need to use the option to delete the data, although we then need to work out why you got the error in point 2. It's going to depend to an extent on what generator you used and how many rows you're trying to create. It may be that you ended up in a situation where it tried to insert a duplicate key for instance.
As for copying the data from customer > address, I'm not sure this will work in one generation session as the data in the source may not yet exist for it to retrieve into addresses yet.
What may be best is if you can mail across the create-scripts for the tables, and details of the settings you had for each table+column in Data Generator to us at support@red-gate.com We've got a ticket open for you, so please put F0050565 in the subject line and we'll try to help out.
Redgate Software