Foreign key refering to multiple tables

Question

I have 4 tables

A(ida, name)
B(ida, B-specific stuff)
C(ida, C-specific stuff)
D(ida, D-specific stuff)

and i want that another table E could refer to just B OR C (not D). What can i write in the

CREATE TABLE E

?

Answer 1

Seems to me that you are trying to use some kind of supertype/subtype -- as opposed to simple vertical partitioning. If so, do introduce a type-discriminator.

Because this is generic example (A, B, C, D ..) it is hard to guess what relates to what, so here are two options as my best guess.

---

Option 1

---

Option 2

Answer 2

You could use a check constraint to enforce that D only references B or C:

create table D
    (
    id int constraint PK_D primary key,
    idb int constraint FK_D_IDB foreign key references B(id),
    idc int constraint FK_D_IDC foreign key references C(id),
    constraint CHK_D_B_OR_C check 
        (
        case when idb is null then 0 else 1 end + 
        case when idc is null then 0 else 1 end = 1
        )
    );

Live example at SQL Fiddle.