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

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

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

Postby monkeygrind » Mon Jan 03, 2011 7:57 pm

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

! 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?
monkeygrind
 
Posts: 28
Joined: Wed Jun 21, 2006 10:57 pm
Location: Seattle-ish

Postby monkeygrind » Tue Jan 04, 2011 8:45 pm

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.
monkeygrind
 
Posts: 28
Joined: Wed Jun 21, 2006 10:57 pm
Location: Seattle-ish

Postby Chris Auckland » Tue Jan 04, 2011 9:04 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests