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

cmasaya

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.

way0utwest

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.

cmasaya

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:


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.



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

cmasaya

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..

and the redgate configuration for this column

sorry, this is the correct picture.


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

way0utwest

You are welcome

way0utwest

Can you provide more schema DDL and sample data that explains this? Is this a compound FK?

Glenn

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 );

way0utwest

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)

cmasaya

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..

and the redgate configuration for this column

way0utwest

Refresh the schema in data generator. If you've saved the changes, not just made in the diagram, it should pick those up.