Options

Foreign Key manual one to many generator

jmebonesjmebones Posts: 4
edited August 13, 2014 3:29AM in SQL Data Generator
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.
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Options
    jmebonesjmebones Posts: 4
    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.
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Options
    jmebonesjmebones Posts: 4
    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.
    · Share on Twitter
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
Sign In or Register to comment.