How to generate a foreign key that respects the referential integrity?
cmasaya
Posts: 4 New member
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.
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
-
way0utwest Posts: 312 Rose Gold 1This 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.
Editor, SQLServerCentral -
cmasaya Posts: 4 New memberway0utwest wrote: »Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
This is the Relational Modelway0utwest 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 Posts: 4 New memberway0utwest 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 asalter 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
Answers
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 should have:
Then a FK declared as
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