Referencing a two column primary key with multiple foreign keys

9
votes

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

3
sqloraclekeysqlplus
Do you get an error? I can't remember if Oracle enforces this, but if A.A and A.B are referenced as FKs, they may each need an individual index, rather than just the composite PK you have defined on them. Try defining an additional index on each of those columns alone in Table A. - Michael Berkowski
So the total indices you have on Table A are: PK (A,B), UNIQUE (C), INDEX (A), INDEX (B) - Michael Berkowski
Hm, it's yelling at me: ORA-00904: : invalid identifier Oh, sorry, I didn't notice your first question, yes, I get an error. ORA-02270: no matching unique or primary key for this column-list - psidhu

3 Answers

14
votes

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

1
votes

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)
);
0
votes

A foreign key always references a PK of another table. Neither A nor B alone are PK's.. You have multiple PK's.

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