复合主键中的外键 [英] Foreign key in composite primary key

问题描述

我有以下3个表：

tblA

  ID  -  PK 
 name 
  

tblB

  ID  -  PK 
 tblAID  -  FK引用tblA.ID 
 pkID2 
  
 
 
 
  tblC  
  ID  -  PK 
 tblAID * 
 fkID2 * 
 ... 
  
我想让这个元组成为（tblB.tblAID，tblB.pkID2）的一个FK  
 
 
 
如果我这样做：
  alter table tblC with check 
添加约束FK_tblC_tblB 
外键（tblAID，fkID2）
引用tblB（tblAID，pkID2）
  
 ：
 
 
引用表'tblB'
中没有与外部引用列列表匹配的主键或候选键键
'FK_tblC_tblB'
 
 
 Ie：我想确保元组对插入 tblC 存在于 tblB 中。但我不能这样做，因为 pkID2 不是一个键。真的 tblB 可以有 tblAID，pkID2 作为复合主键。但是 tblAID 仍然必须是 tblA.ID 的FK。
 
 
 $ b    
 $ b 
或从这两个字段创建一个新的唯一键。外键不需要引用主键。
 
 
但是tblAID仍然必须是tblA.ID的FK。 
 
 
这将仍然有效。主键或唯一键中的列也可以参与其他约束，包括外键约束。
 
I have the following 3 tables:

tblA
ID - PK
name
tblB
ID - PK
tblAID - FK references tblA.ID
pkID2
tblC
ID - PK
tblAID *
fkID2 *
...
I want to make this tuple a FK of (tblB.tblAID, tblB.pkID2)

If I do this:
alter table tblC with check 
add constraint FK_tblC_tblB 
foreign key (tblAID, fkID2) 
references tblB (tblAID, pkID2)
I get an error message:

  
There are no primary or candidate keys in the referenced table 'tblB'
  that match the referencing column list in the foreign key
  'FK_tblC_tblB'
I.e.: I want to ensure that the tuple pair inserted into tblC exists in tblB. But I cannot do that as pkID2 is not a key. Really tblB could have tblAID, pkID2 as a composite primary key. But then tblAID would still have to be a FK of tblA.ID.
解决方案

  
Really tblB could have tblAID, pkID2 as a composite primary key.
Or create a new unique key from these two fields. A foreign key does not need to refer to a primary key.

  
But then tblAID would still have to be a FK of tblA.ID.
That will still work. A column in a primary or unique key can also participate in other constraints, including foreign key constraints.

                        
这篇关于复合主键中的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！