How to generate a foreign key that respects the referential integrity?

I need to generate information for a column whose foreign key depends on another column of the same table (previously generated). How can I ensure that the referential integrity of the generated information is respected in the generation process?

Example:
CODE FOR SALE
PRODUCT CODE
SUB-PRODUCT CODE

The Product code was generated correctly with reference to the product table, but the SUB-PRODUCT CODE must belong to the set of sub-products related to the PRE-GENERATED PRODUCT CODE.
Tagged:

Best Answers

  • way0utwestway0utwest CO, USAPosts: 190 Gold 3
    Accepted Answer
    This can't quite be correct. The SubProduct table has a single PK (SubProductCode). When setting the FK for ForSale, you should get the error:
    Msg 1776, Level 16, State 0, Line 23
    There are no primary or candidate keys in the referenced table 'dbo.SubProduct' that match the referencing column list in the foreign key 'fk_ForSale_SubProductCode_within_ProductCode'.
    Msg 1750, Level 16, State 1, Line 23
    Could not create constraint or index. See previous errors.
    

    The FK in ForSale needs to match the PK in SubProductCode. Both SubProductCode and ProductCode should be in the PK. The ordering needs to be the same.

    However, if you have this DRI setup, then Data Generator will recognize the FK relationship and choose that. Since the FK will be on product and subproduct, the data will match.
    gs3icvkpffya.jpg
  • cmasayacmasaya Posts: 4 New member
    Accepted Answer
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?

    This is the Relational Model
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
    way0utwest wrote: »
    Can you provide more schema DDL and sample data that explains this? Is this a compound FK?

    Thank You for your Help, this is the exactly situation:
    1. I have a table of "DEPARTAMENTOS" is like State
    2. I have a table of "MUNICIOPIOS" is like city
    3. the DEPARTAMENTOS has a 1 to n relation with a MUNICIOPIOS TABLE, as you can see in the next picture:
    nprienm3e0yx.jpg

    4. the situation is that when I generate information for the tabal "AFFILIATE" the department is generated OK, but the municipality generated does not belong to the related department. Lo can be seen below.

    j7sqajdoec1i.jpg

    finaly, this is the way as i'm generating the information in RedGate.
    itcdvb97bfh9.png
    bcw4qq2btnzd.png


  • cmasayacmasaya Posts: 4 New member
    Accepted Answer
    cmasaya wrote: »
    way0utwest wrote: »
    You don't have correct DRI. If you want to constrain the data in Affiliate, your Municipio should have a PK on both IDDepartmento and IDMuicipo. You are asking them to both be a FK, but you haven't declared that. There's no definition of that relationship in your diagram.

    You should have:
    create table Municipio
    ( id_municipio int not null
    , id_departmento not null
    , nombre_municipio varchar(200)
    , constraint MunicipioPK primary keyt (id_departmento, id_municipio)
    )
    

    Then a FK declared as
    alter table Affiliate add constraint Affiliate_Municipio_FK Foreign Key (id_departmento, id_municipio) references Municipio (id_departmento, id_municipio)
    

    Thanks for the support
    Make the suggested change in the composite keys scheme, but I do not work as I wished, next the changes in the database..
    gqvcortl5l27.png
    and the redgate configuration for this column
    pxqeyln0gdnj.png

    sorry, this is the correct picture.
    2zcat7nn1cpc.png

    Thank you !!

    The solution consisted of the following combination:
    1. Keys composed in the definition of the table "Municipalities", as you indicated.
    2. The generation using foreign keys (automatic mode)

    Thank you very much again
  • way0utwestway0utwest CO, USAPosts: 190 Gold 3
    Accepted Answer
    You are welcome

Answers

  • Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
  • GlennGlenn Chattanooga, TNPosts: 2 Bronze 3
    Assuming the following table structure, the FK you would need is in the last lines of code. Is this what you need?

    CREATE TABLE Product ( ProductCode VARCHAR(30) PRIMARY KEY
    , ProductDesc VARCHAR(100));

    CREATE TABLE SubProduct ( SubProductCode VARCHAR(30) PRIMARY KEY
    , ProductCode VARCHAR(30)
    , SubProductDesc VARCHAR(100));
    ALTER TABLE dbo.SubProduct
    ADD CONSTRAINT fk_SubProduct_ProductCode
    FOREIGN KEY( ProductCode )
    REFERENCES dbo.Product( ProductCode );

    CREATE TABLE ForSale ( SaleCode VARCHAR(30)
    , ProductCode VARCHAR(30)
    , SubProductCode VARCHAR(30)
    , SaleDesc VARCHAR(100));
    ALTER TABLE dbo.SubProduct
    ADD CONSTRAINT fk_ForSale_ProductCode
    FOREIGN KEY( ProductCode )
    REFERENCES dbo.Product( ProductCode );


    ALTER TABLE ForSale
    ADD CONSTRAINT fk_ForSale_SubProductCode_within_ProductCode
    FOREIGN KEY( ProductCode, SubProductCode )
    REFERENCES dbo.SubProduct( ProductCode, SubProductCode );
  • You don't have correct DRI. If you want to constrain the data in Affiliate, your Municipio should have a PK on both IDDepartmento and IDMuicipo. You are asking them to both be a FK, but you haven't declared that. There's no definition of that relationship in your diagram.

    You should have:
    create table Municipio
    ( id_municipio int not null
    , id_departmento not null
    , nombre_municipio varchar(200)
    , constraint MunicipioPK primary keyt (id_departmento, id_municipio)
    )
    

    Then a FK declared as
    alter table Affiliate add constraint Affiliate_Municipio_FK Foreign Key (id_departmento, id_municipio) references Municipio (id_departmento, id_municipio)
    
  • cmasayacmasaya Posts: 4 New member
    way0utwest wrote: »
    You don't have correct DRI. If you want to constrain the data in Affiliate, your Municipio should have a PK on both IDDepartmento and IDMuicipo. You are asking them to both be a FK, but you haven't declared that. There's no definition of that relationship in your diagram.

    You should have:
    create table Municipio
    ( id_municipio int not null
    , id_departmento not null
    , nombre_municipio varchar(200)
    , constraint MunicipioPK primary keyt (id_departmento, id_municipio)
    )
    

    Then a FK declared as
    alter table Affiliate add constraint Affiliate_Municipio_FK Foreign Key (id_departmento, id_municipio) references Municipio (id_departmento, id_municipio)
    

    Thanks for the support
    Make the suggested change in the composite keys scheme, but I do not work as I wished, next the changes in the database..
    gqvcortl5l27.png
    and the redgate configuration for this column
    pxqeyln0gdnj.png
  • Refresh the schema in data generator. If you've saved the changes, not just made in the diagram, it should pick those up.
Sign In or Register to comment.