Overriding Foreign Key Generator

The simplest way to populate your database with test data.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, Lionel, Emma A, sherr

Overriding Foreign Key Generator

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

We have a schema as follows:
Code: Select all
CREATE TABLE record (
   recordId uniqueidentifier NOT NULL,
 CONSTRAINT PK_record PRIMARY KEY CLUSTERED (recordId)
)

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?

Thanks,
Dunmail
Dunmail
 
Posts: 3
Joined: Tue Nov 29, 2011 2:42 pm
Location: UK

Postby james.billings » Wed Nov 30, 2011 5: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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Also wish to override/customize/replace FK Generator

Postby snyder_rls » Fri Jan 06, 2012 7: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
snyder_rls
 
Posts: 1
Joined: Fri Jan 06, 2012 6:53 pm

Postby paulo.morgado » Fri Jan 13, 2012 4: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
Portugal
Web Site
Weblog
Twitter
paulo.morgado
 
Posts: 52
Joined: Wed Aug 16, 2006 3:41 pm
Location: Portugal

Postby tilo23 » Mon Jan 16, 2012 1: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
tilo23
 
Posts: 19
Joined: Thu Dec 22, 2011 3:56 pm
Location: Berkshire, UK

Postby northupj » Tue Jan 31, 2012 9: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.
northupj
 
Posts: 2
Joined: Wed Jan 13, 2010 5:06 pm

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

Hi,

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.

Thanks
pmap_anil
 
Posts: 2
Joined: Mon Feb 13, 2012 12:46 pm

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

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

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

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

Postby pvearchitect » Mon Mar 18, 2013 11: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.
pvearchitect
 
Posts: 1
Joined: Mon Mar 18, 2013 11:49 pm


Return to SQL Data Generator 2

Who is online

Users browsing this forum: No registered users and 0 guests