Overriding Foreign Key Generator

Dunmail

We have a schema as follows:

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

James B

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.

snyder_rls

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

paulo_morgado

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?

tilo23

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.

northupj

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.

pmap_anil

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

yaosen

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

CraigEddy

I am definitely in the camp of wanting to specify SPECIFIC foreign key values.

pvearchitect

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.