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

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

  way0utwest September 27, 2017 16:26

  - 0 +
• 
  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 );
  

  Glenn September 27, 2017 16:36

  - 0 +
• 
  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.
  
  

  way0utwest September 27, 2017 17:26

  - 0 +
• 

  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 September 27, 2017 18:34

  - 0 +
• 
  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)
  

  way0utwest September 27, 2017 18:42

  - 0 +
• 

  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
  
  

  cmasaya September 27, 2017 19:18

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

  way0utwest September 27, 2017 19:22

  - 0 +
• 

  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

  cmasaya September 27, 2017 19:29

  - 0 +
• 
  You are welcome

  way0utwest September 27, 2017 19:33

  - 0 +

Add comment

Please sign in to leave a comment.