SQL 使用多个外键引用两列主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14543846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Referencing a two column primary key with multiple foreign keys
提问by psidhu
Take the following two tables in Oracle:
在Oracle中取以下两个表：
Create Table A
( A int, B int, C int,
Constraint pk_ab Primary Key(A, B),
Unique (C)
);
Create Table B
( D int, E int, F int,
Constraint fk_d Foreign Key (D) References A(A),
Constraint fk_e Foreign Key (E) References A(B)
);
Why doesn't this statement work? Or more specifically, why shouldn't it work? The reason I'm trying to create this type of relation is say, in the future, I want to delete B.D, but keep the relation FK_E.
为什么这个声明不起作用？或者更具体地说，为什么它不应该工作？我尝试创建这种类型的关系的原因是说，将来，我想删除B.D，但保留该关系FK_E。
I'm getting the error:
我收到错误：
ORA-02270: no matching unique or primary key for this column-list
ORA-02270: 此列列表没有匹配的唯一键或主键
回答by APC
"Why doesn't this statement work? Or more specifically, why shouldn't it work? "
“为什么这个声明不起作用？或者更具体地说，为什么它不应该起作用？”
You have defined the primary key on A as a compound of two columns (A,B). Any foreign key which references PK_AB must match those columns in number. This is because a foreign key must identify a single rowin the referenced table which owns any given row in the child table. The compound primary key means column A.A can contain duplicate values and so can column A.B; only the permutations of (A,B) are unique. Consequently the referencing foreign key needs two columns.
您已将 A 上的主键定义为两列 (A,B) 的复合。任何引用 PK_AB 的外键都必须在数量上与这些列匹配。这是因为外键必须标识引用表中的单行，该行拥有子表中的任何给定行。复合主键意味着 AA 列可以包含重复值，AB 列也可以包含重复值；只有 (A,B) 的排列是唯一的。因此，引用外键需要两列。
Create Table B
( D int, E int, F int,
Constraint fk_de Foreign Key (D,E) References A(A,B)
);
"Since there are multiple PK's that table B references"
“由于表 B 引用了多个 PK”
Wrong. B references a single primary key, which happens to comprise more than one column,
错误的。B 引用一个主键，它恰好包含多列，
" say, in the future, I want to delete B.D, but keep the relation fk_e. "
“说吧，以后想删BD，但保留fk_e关系。”
That doesn't make sense. Think of it this way: D is not a property of B, it is an attribute B inherits through its dependence on table A.
那没有意义。可以这样想：D 不是 B 的属性，它是 B 通过其对表 A 的依赖而继承的属性。
One way to avoid this situation is to use a surrogate (or synthetic) key. Compound keys are often business keys, hence their columns are meaningful in a business context. One feature of meaningful column values is that they can change, and cascading such changes to foreign keys can be messy.
避免这种情况的一种方法是使用代理（或合成）键。复合键通常是业务键，因此它们的列在业务上下文中是有意义的。有意义的列值的一个特性是它们可以更改，并且将此类更改级联到外键可能会很麻烦。
Implementing a surrogate key would look like this:
实现代理键看起来像这样：
Create Table A
( id int not null, A int, B int, C int,
Constraint pk_a Primary Key(ID),
constraint uk_ab Unique (A,B)
);
Create Table B
( a_id int, F int,
Constraint fk_n_a Foreign Key (A_ID) References A(ID)
);
Of course, you could kind of do this using the schema you posted, as you already have a single column constraint on A(C). However, I think it is bad practice to reference unique constraints rather than primary keys, even though it's allowed. I think this partly because unique constraints often enforce a business key, hence meaning, hence the potential for change, but mainly because referencing primary keys just is the industry standard.
当然，您可以使用您发布的架构来执行此操作，因为您已经在 A(C) 上有一个单列约束。但是，我认为引用唯一约束而不是主键是不好的做法，即使它是允许的。我认为这部分是因为唯一约束通常会强制执行业务键，因此意义，因此有可能发生变化，但主要是因为引用主键只是行业标准。
回答by peterm
Try create two separate indexes for column's Aand Bbefore creating table B
尝试创建一个列的两个单独的索引A和B创建表之前B
CREATE INDEX a_idx ON A (A);
CREATE INDEX b_idx ON A (B);
But probably you need a compound FK on table B
但可能你需要一个复合 FK 放在桌子上 B
Create Table B
( D int, E int, F int,
Constraint fk_d Foreign Key (D,E) References A(A,B)
);
回答by user1974729
A foreign key always references a PK of another table. Neither A nor B alone are PK's.. You have multiple PK's.
外键总是引用另一个表的 PK。A 和 B 都不是 PK 的。你有多个 PK。
Constraint fk_d Foreign Key (D,E) References A(A,B)
Also the database cannot validate a partial null multiple foreign key, so I think the check constraint also needs to be added to the table.
此外，数据库无法验证部分空多个外键，因此我认为还需要将检查约束添加到表中。
alter table B add constraint check_nullness
check ( ( D is not null and E is not null ) or
( D is null and E is null ) )
