CODEWITHSUNDEEP
sqldatabase-designforeign-keysprimary-keyvertical-partitioning
As As
As As

Reputation: 2107

Foreign key refering to multiple tables

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

?

Upvotes: 2

Views: 1640

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

enter image description here

Option 2

enter image description here

Upvotes: 2

Andomar
Andomar

Reputation: 238166

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.

Upvotes: 1

Related Questions