Red Gate forums :: View topic - What is the easiest way to manage FK tables/references?
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

What is the easiest way to manage FK tables/references?

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



Joined: 21 Jun 2006
Posts: 28
Location: Seattle-ish

PostPosted: Mon Jan 03, 2011 7:57 pm    Post subject: What is the easiest way to manage FK tables/references? Reply with quote

I think I got my project set up the way I want, however two of my tables are giving me the ominous warning:

Quote:
! Deleting data from this table may fail. This table is referenced by a foreign key constraint on table [dbo].[TableName]. Table [dbo].[TableName] may have data, therefore the deletion may fail.


Ideally the sequence would be:

1. Delete any tables that have a FK constraint on the primary table
2. Delete the primary table
3. Repopulate the primary table
4. Add data to the FK tables

Or, perhaps the generator could drop all FK constraints from the tables, add the data, then add the constraints back in.

I guess I could create scripts to do this and use the Scripts tab in the project setup area of the tool, but it seems to me that the SQL Generator should do this for me to prevent me from missing something and it then blowing up.

It would seem to me that if the SQL Data Compare can figure out dependency orders of moving data then the SQL Generator could do the same, and ensure that data is deleted / inserted in the right order (or, dropping/re-adding constraints).

Or am I missing something here?
Back to top
View user's profile Send private message
monkeygrind



Joined: 21 Jun 2006
Posts: 28
Location: Seattle-ish

PostPosted: Tue Jan 04, 2011 8:45 pm    Post subject: Reply with quote

As as aside, from a feature point of view, it would be sweet to also have a checkbox to deactive (or drop/add) triggers to the tables before generating the data.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Jan 04, 2011 9:04 pm    Post subject: Reply with quote

Thanks for your post.

As you have found, the only current workaround for this is to use the scripts tab and disable the FK relationship yourself before you insert the data. I believe the thinking was that if you're going to generate data for a table with a FK relationship, that you would also want to generate data for the FK table too.

There are a few situations where this isn't always the case, and those situations are not easy to deal with using SQL Data Generator.

We have an open feature request the allow the disabling of foreign keys, similar to SQL Data Compare. With this feature we will have to trust that the user will not generate data in such as way to make the FK invalid when it is enabled at the end. Anyway, I have added you as another vote for SDG-900.

With regards to the triggers query. We have the project option to fire Insert or Delete triggers, so I assume if these options are unchecked that the triggers will not fire when you insert or delete.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
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