Overriding Foreign Key Generator

The simplest way to populate your database with test data.

Overriding Foreign Key Generator

Postby Dunmail » Tue Nov 29, 2011 2:30 pm

We have a schema as follows:
Code: Select all
   recordId uniqueidentifier NOT NULL,

CREATE TABLE recordDescription(
   recordId uniqueidentifier NOT NULL,
   locale nchar(8) NOT NULL,
   translation varchar(50) NOT NULL
ALTER TABLE recordDescription WITH CHECK ADD  CONSTRAINT FK_recordDescription_record FOREIGN KEY(recordId)
REFERENCES record (recordId)

Because of the FK constraint, SDG forces use of a Foreign Key Generator for recordDescription.recordId. However, we want to load the data from CSV files so that we can ensure that translations match the correct records! Is there any way (short of permanently removing the FK constraint) that we can configure SDG to load all the data for both tables from CSV files?

Posts: 3
Joined: Tue Nov 29, 2011 1:42 pm
Location: UK

Postby james.billings » Wed Nov 30, 2011 4:20 pm

Thanks for your post.

Unfortuately if you have FK's in place, Data Generator will always use the FK generator. Asides from dropping the relationships (temporarily) there's nothing you can do I'm afraid.
We do have one or two feature requests in this area so that you can configure it; but I have no ETA on them being looked at as yet.
Posts: 1121
Joined: Wed Jun 16, 2010 10:10 am
Location: Red Gate

Also wish to override/customize/replace FK Generator

Postby snyder_rls » Fri Jan 06, 2012 6:08 pm

We also have a schema for which data cannot be generated without the ability to override or replace the FK Generator.

We have a BusinessEntity table, that has 9 types (e.g. Persons vs Organizations). Address FKs to BusinessEntity, as do Person and Organization. We can't keep BusinessEntity rows from being:
- referenced by the wrong table - with respect to type
- referenced by multiple tables of different types

Our painful workaround is to create specific BusinessEntity tables, temporarily re-linking foreign keys, generate to those tables, and merge back after data generation (manageable because our keys are GUIDs).

A lot of SQL for the pre-scripts (that also has to be executed before editing the .sqlgen package so the generation can be configured), and post-scripts
Posts: 1
Joined: Fri Jan 06, 2012 5:53 pm

Postby paulo.morgado » Fri Jan 13, 2012 3:12 pm

I'm also facing a problem where I need to generate rows referencing a subset of the referenced tables.

Isn't this a common use scenario?
Paulo Morgado
Web Site
Posts: 52
Joined: Wed Aug 16, 2006 2:41 pm
Location: Portugal

Postby tilo23 » Mon Jan 16, 2012 12:07 pm

The only way I've found so far is to temporarily drop constraints just before the SDG project runs, and include a script after data generation to restore them.
It would be nice to enable drop the constraints script in SDG before data is generated and allow to modify data inside the SDG as if the constraints had already been dropped.
Inside the Dilbert's world
Posts: 19
Joined: Thu Dec 22, 2011 2:56 pm
Location: Berkshire, UK

Postby northupj » Tue Jan 31, 2012 8:14 pm

Just wanted to add my voice to this. I am continually finding scenarios where I want to control the foreign key sampling - either by restricting to a subset or by specifying a weighted distribution. I can't seem to find any way to do this other than to modify my db schema to remove the foreign key constraints before running the data generator tool and then changing the generator to something more useful than the foreign key generator and then re-enabling the constraints after running the tool. This is too clunky a workaround for my needs.
Posts: 2
Joined: Wed Jan 13, 2010 4:06 pm

Postby pmap_anil » Mon Feb 13, 2012 12:14 pm


I also faced the same problem earlier. For this, the dropping of constraints before the data generation is fine. And as soon as we drop the constraints, generate data from a csv file for the table which you have foreign key relationship. Once the data is generated for that table, enable the constraints. This would help in generating the data for foreign-key tables from a csv file. And it also maintains the correct structural design of the table in that database. I think, this process would surely help you.

Posts: 2
Joined: Mon Feb 13, 2012 11:46 am

Postby yaosen » Thu Mar 22, 2012 6:12 pm

We also have a schema for which data cannot be generated without the ability to override or replace the FK Generator. Image
Posts: 2
Joined: Mon Mar 19, 2012 10:19 am

Postby CraigEddy » Fri Oct 26, 2012 1:14 pm

I am definitely in the camp of wanting to specify SPECIFIC foreign key values.
Posts: 20
Joined: Fri Sep 28, 2012 1:59 pm

Postby pvearchitect » Mon Mar 18, 2013 10:54 pm

I also have a similar situation where I have an FK from a list of reference values but, due to a constraint, can only have a subset of values placed in the table. I would like to have the ability to apply a filter to the FK to restrict the generation to allowed values.
Posts: 1
Joined: Mon Mar 18, 2013 10:49 pm

Return to SQL Data Generator Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest