[SOLVED] "polymorphism" for FOREIGN KEY constraints

"polymorphism" for FOREIGN KEY constraints

There is this field in a table:

room_id INT NOT NULL CONSTRAINT room_id_ref_room REFERENCES room

I have three 2 tables for two kinds of rooms: standard_room and family_room

How to do something like this:

room_id INT NOT NULL CONSTRAINT room_id_ref_room REFERENCES standard_room or family_room

I mean, room_id should reference either standard_room or family_room.
Is it possible to do so?

Solution

Here is the pattern I've been using.

CREATE TABLE room (
    room_id serial primary key,
    room_type VARCHAR not null,

    CHECK CONSTRAINT room_type in ("standard_room","family_room"),
    UNIQUE (room_id, room_type)
);

CREATE_TABLE standard_room (
    room_id integer primary key,
    room_type VARCHAR not null default "standard_room",

    FOREIGN KEY (room_id, room_type) REFERENCES room (room_id, room_type),
    CHECK CONSTRAINT room_type  = "standard_room"
);
CREATE_TABLE family_room (
    room_id integer primary key,
    room_type VARCHAR not null default "family_room",

    FOREIGN KEY (room_id, room_type) REFERENCES room (room_id, room_type),
    CHECK CONSTRAINT room_type  = "family_room"
);

That is, the 'subclasses' point at the super-class, by way of a type descriminator column (such that the pointed to base class is of the correct type, and that primary key of the super class is the same as the child classes.