引用具有多个外键的两列主键

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:此列列表没有匹配的唯一键或主键

3 个解决方案

#1

"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 row in 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的任何外键都必须与这些列匹配。这是因为外键必须标识引用表中的一行,该行拥有子表中的任何给定行。复合主键表示列A.A可以包含重复值,因此列A.B;只有(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. "

“说,在将来,我想删除B.D,但保持关系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)上有一个列约束。但是,我认为引用唯一约束而不是主键是不好的做法,即使它是允许的。我认为这部分是因为独特的约束通常强制执行业务密钥,因此意味着变更的可能性,但主要是因为引用主键只是行业标准。

#2

Try create two separate indexes for column's A and B before creating table B

在创建表B之前,尝试为列A和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

但是你可能需要在表B上使用复合FK

Create Table B
( D int, E int, F int,
  Constraint fk_d Foreign Key (D,E) References A(A,B)
);

#3

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.

此外,数据库无法验证部分null多个外键,因此我认为还需要将检查约束添加到表中。

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 ) )

#1