How to add a foreign key referencing a composite primary key when you don't have all the columns, but can get them via another foreign key?

Question

I have the following tables:

create table AAA
(
    AAA_ID NUMBER
);

alter table AAA
    add constraint AAA_PK
    primary key (AAA_ID);

create table BBB
(
    BBB_ID NUMBER,
    AAA_ID NUMBER
);

alter table BBB
    add constraint BBB_PK
    primary key (BBB_ID, AAA_ID); --IMPORTANT

alter table BBB
    add constraint BBB_FK_01
    foreign key (AAA_ID)
    references AAA (AAA_ID);

create table CCC
(
    CCC_ID NUMBER,
    AAA_ID NUMBER
);

alter table CCC
    add constraint CCC_PK
    primary key (CCC_ID); --IMPORTANT

alter table CCC
    add constraint CCC_FK_01
    foreign key (AAA_ID)
    references AAA (AAA_ID);

create table CCC_BBB
(
    CCC_ID NUMBER,
    CCC_BBB_ID NUMBER,
    BBB_ID NUMBER
);

alter table CCC_BBB
    add constraint CCC_BBB_PK
    primary key (CCC_ID, CCC_BBB_ID);

alter table CCC_BBB
    add constraint CCC_BBB_FK_01
    foreign key (CCC_ID)
    references CCC (CCC_ID);

I want to add a foreign key constraint in CCC_BBB referencing BBB. BBB_ID is present directly in CCC_BBB, but there's no AAA_ID. However, AAA_ID is present in CCC, which is referenced by CCC_BBB_FK_01. Is it possible to express this constraint in SQL?

I'd prefer a standard-compliant solution, but an Oracle-specific one is also welcome.

Edit. I've been asked to clarify what the real world problem is, so this is my attempt (I'd rather not discuss the real problem domain here):

AAA is a process. BBB is a process step. Several processes have steps with similar names, but different meanings, so the table has a composite key. CCC is a process instance. Their IDs are unique, so the table doesn't have a composite PK. CCC_BBB is the list of steps taken in the specific instance.

I need to ensure that the list of steps for a process instance includes only those steps that are allowed for that process.

Answer 1

You could use a materialized view to join the two tables and then add the foreign key to this:

CREATE MATERIALIZED VIEW LOG ON CCC_BBB
   WITH SEQUENCE, ROWID(CCC_ID,CCC_BBB_ID,BBB_ID)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW CCC_BBB_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT CCC_ID,
             CCC_BBB_ID,
             BBB_ID,
             AAA_ID
      FROM   CCC_BBB b
             INNER JOIN
             CCC c
             ON ( b.CCC_ID = c.CCC_ID );

ALTER TABLE CCC_BBB_MV ADD CONSTRAINT ccc_bbb_mv__fk
  FOREIGN KEY ( AAA_ID, BBB_ID ) REFERENCES BBB ( AAA_ID, BBB_ID );

(Above code is untested but should be illustrative of a solution)

While it may work, it is a bit of a hack, and you would use less storage space to just add AAA_ID to your CCC_BBB table.

Answer 2

Your problem seems to stem from composite keys only halfhartedly applied.

Let's start with technical non-composite keys first. I've renamed the table and column names slightly to enhance readability. Primary keys are bold.

• A (A_ID, col1, col2, ...)
• AB (AB_ID, A_ID, col1, col2, ...)
• AC (AC_ID, A_ID, col1, col2, ...)
• ABC (ABC_ID, AC_ID, AB_ID, col1, col2, ...)

Here we have the situation you describe: AC and AB are children to A and ABC is child to both AB and AC, but the DBMS can not guarantee that BC contains B and C that both belong to the same A. This is a well-known drawback in the purely ID based database design; it fails to guarantee consistency over the tables' hierarchy.

Now the same with composite keys (which is very common with natural keys, but works with technical IDs, too):

• A (A_ID, col1, col2, ...)
• AB (A_ID, B_ID, col1, col2, ...)
• AC (A_ID, C_ID, col1, col2, ...)
• ABC (A_ID, B_ID, C_ID, col1, col2, ...)

Here consistency is guaranteed, because the complete parent key is always part of the primary key.

What you are doing is a mix. You are applying a composite key to the last table, but in all parent tables you don't, so it's too late. You have used a non-composite ID concept and suffer from its consistency drawback.

• A (A_ID, col1, col2, ...)
• AB (AB_ID, A_ID, col1, col2, ...)
• AC (AC_ID, A_ID, col1, col2, ...)
• ABC (AC_ID, ACSUB_ID, AB_ID, col1, col2, ...)