Is this a correct foreign key relationship standard?

Question

Let's assume I have 3 tables and sorted from parents to child:

• Table A (column AId)
• Table B (columns BId, AId)
• Table C (columns CId, AId, BId)

Table B.AId is referencing Table A.AId.

Table C.BId is linked to Table B.BId and Table C.AId is linked to Table B.AId - not Table A.AId.

Can I know whether this is correct standard or not? Explain with more information.

Answer 1

Answer might depend on database you actually use.

I'm on Oracle, and - from its perspective - your approach wouldn't work because foreign key must reference primary (or unique) key in another table.

It means that the first two tables can be created with no problems:

SQL> create table table_a
  2    (aid number primary key);

Table created.

SQL> create table table_b
  2    (bid number primary key,
  3     aid number references table_a (aid));

Table created.

However, table_c would fail because aid is trying to reference table_b.aid, but that column is not primary key in table_b:

SQL> create table table_c
  2    (cid number primary key,
  3     bid number references table_b (bid),
  4     aid number references table_b (aid));
   aid number references table_b (aid))
                                  *
ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list

However, if table_c.aid references table_a.aid (a primary key column), everything is OK:

SQL> create table table_c
  2    (cid number primary key,
  3     bid number references table_b (bid),
  4     aid number references table_a (aid));

Table created.

---

Alternative approach (although this is NOT what you described) would be a composite primary (and then foreign) key:

SQL> drop table table_c;

Table dropped.

SQL> drop table table_b;

Table dropped.

Composite primary key:

SQL> create table table_b
  2    (bid number,
  3     aid number references table_a (aid),
  4     --
  5     constraint pk_b primary key (bid, aid));

Table created.

table_c still can't reference table_b's columns separately:

SQL> create table table_c
  2    (cid number primary key,
  3     bid number references table_b (bid),
  4     aid number references table_b (aid));
   bid number references table_b (bid),
                                  *
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list

Composite foreign key is OK:

SQL> create table table_c
  2    (cid number primary key,
  3     bid number,
  4     aid number,
  5     --
  6     constraint fk_cb foreign key (bid, aid) references table_b (bid, aid));

Table created.

SQL>

---

If your database supports foreign keys that reference any column (which doesn't have to be primary/unique key), you'd probably be able to create such a data model. Is it correct? Can't tell, you should know why you want to reference columns that way. If it were me, I'd reference table_a.aid from table_c.aid.