Red Gate forums :: View topic - Working with Foreign Keys
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

Working with Foreign Keys

Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
aespaldi



Joined: 27 Jul 2011
Posts: 1

PostPosted: Wed Jul 27, 2011 7:06 am    Post subject: Working with Foreign Keys Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Thu Jul 28, 2011 7:20 pm    Post subject: Reply with quote

Thanks for your post.

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group