且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

同时指向多个表的外键

更新时间:2023-11-09 22:20:52

我强烈建议您使用两列,每个外键使用一列.您可能希望这些列可以为空,因此一行可能指向"一个相关表或另一个相关表(甚至两者).

I highly recommend you use two columns, one per each foreign key. You probably want those columns to be nullable, so a row may "point" to one related table or the other (maybe even none, or both).

从技术上讲,可以使用单列存储两个相关表的外键.但是,您将无法随意指向一个,因为外键约束将强制在两个相关表中都存在一个非null值总是.您将需要删除外键约束,这是一个很大的禁忌.

It's technically possible to use a single column that stores a foreign key to both related tables. However, you wouldn't be able to point to one or the other at will, since the foreign key constraint will enforce a non-null value to exist in both related tables always. You would need to remove the foreign key constraint, and that's a big no-no.

简而言之,这就是我的看法:

In short, this is how I see it:

clientPayment(
  rfcM varchar(12) references clientM, -- nullable by default
  rfcF varchar(13) references clientF, -- nullable by default
  constraint one_and_only_one_fk check (
    clientM is null and clientF is not null or
    clientM is not null and clientF is null
  ),
  some other data
)

该约束可防止两个fks同时为null或同时不为null.

The constraint prevents both fks to be null at the same time, or both to be not null at the same time.