Red Gate forums :: View topic - Overriding Foreign Key Generator
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 2
SQL Data Generator 2 forum

Overriding Foreign Key Generator

Search in SQL Data Generator 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Dunmail



Joined: 29 Nov 2011
Posts: 3
Location: UK

PostPosted: Tue Nov 29, 2011 3:30 pm    Post subject: Overriding Foreign Key Generator Reply with quote

We have a schema as follows:
Code:
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
_________________
www.blackpear.com
www.stanegatesolutions.com
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Nov 30, 2011 5:20 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
snyder_rls



Joined: 06 Jan 2012
Posts: 1

PostPosted: Fri Jan 06, 2012 7:08 pm    Post subject: Also wish to override/customize/replace FK Generator Reply with quote

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
Back to top
View user's profile Send private message
paulo.morgado



Joined: 16 Aug 2006
Posts: 52
Location: Portugal

PostPosted: Fri Jan 13, 2012 4:12 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
tilo23



Joined: 22 Dec 2011
Posts: 19
Location: Berkshire, UK

PostPosted: Mon Jan 16, 2012 1:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
northupj



Joined: 13 Jan 2010
Posts: 2

PostPosted: Tue Jan 31, 2012 9:14 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
pmap_anil



Joined: 13 Feb 2012
Posts: 2

PostPosted: Mon Feb 13, 2012 1:14 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
yaosen



Joined: 19 Mar 2012
Posts: 2

PostPosted: Thu Mar 22, 2012 7:12 pm    Post subject: Reply with quote

We also have a schema for which data cannot be generated without the ability to override or replace the FK Generator.
Back to top
View user's profile Send private message
CraigEddy



Joined: 28 Sep 2012
Posts: 16

PostPosted: Fri Oct 26, 2012 2:14 pm    Post subject: Reply with quote

I am definitely in the camp of wanting to specify SPECIFIC foreign key values.
Back to top
View user's profile Send private message
pvearchitect



Joined: 18 Mar 2013
Posts: 1

PostPosted: Mon Mar 18, 2013 11:54 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
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