Foreign Key manual one to many generator

jmebones

I am copying data between identical tables in a separate database.

I want the column foreign key generator to not be unique but still limited to ids in a third table. Right now the foreign key generator enforces a unique constraint on the column; one to one, I need a one to many relationship.

Thanks.

Brian Donahue

Hello,

I'm afraid I do not completely understand the issue. Can you provide a small sample of this schema? AFAIK the foreign key generator is like a foreign key - one-to-many. It would not make a lot of sense to make the reference side of the relationship be unique as the referenced side should be unique.

jmebones

Say I have a table participants with PK part_id and another table DeliveryAddress with foreign key part_id.

If i setup the foreign key on DeliveryAddress back to the participants table then it will only select unique part_id when a participant could have multiple delivery addresses.

There is a hint to the right of the "Population method" on the Foreign key generator UI stating: If the foreign key references a single column, each value in the referenced column is only used once. I have tested this and it creates a one to one foreign key.

Brian Donahue

If I have the following schema:

CREATE TABLE t1 (
id INT PRIMARY KEY not NULL,
DATA nvarchar(50)
)
GO
CREATE TABLE t2 (
id INT PRIMARY KEY NOT NULL,
t1id INT)
GO
ALTER TABLE [t2] ADD FOREIGN KEY (t1id) REFERENCES t1 (id)
GO

The generator chosen for t1id is a foreign key automatic generator. The default settings are "repeat key values at random". You can also have "repeat key values between x and y times", so you can fine-tune how many times the keys are repeated in the one-to many relationship.

jmebones

We need the data to be valid. When the keys are repeated at random this breaks the data validity

e.g. A cart table with a TotalCost calculated from the products in the cart. If the data is being copied from another data source then the TotalCost is not valid.

Brian Donahue

In the example, t1.id is a primary key and SQL Data Generator will generate all unique data for that, naturally. t2.t1id is the foreign key and that means the data contains repeating data derived from t1.id. If the data in t2.t1id did not repeat, that would define a 1:1 relationship rather than 1:x.