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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With