What is the easiest way to manage FK tables/references?
monkeygrind
Posts: 40
I think I got my project set up the way I want, however two of my tables are giving me the ominous warning:
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?
! 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?
Comments
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.