Referencing a two column primary key with multiple foreign keys

Question

Take the following two tables in 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.

I'm getting the error:

ORA-02270: no matching unique or primary key for this column-list

Answer 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.

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"

Wrong. B references a single primary key, which happens to comprise more than one column,

" say, in the future, I want to delete B.D, but keep the relation 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.

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.

Answer 2

Try create two separate indexes for column's A and B before creating table 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

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