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.
Here's the same SQL from the accepted answer that works for PostGres 12.8. There's a few issues not only the CREATE_TABLE syntax mistake:
CREATE TABLE room ( room_id serial primary key, room_type VARCHAR not null, CONSTRAINT room_in_scope CHECK (room_type in ('standard_room','family_room')), CONSTRAINT unique_room_type_combo UNIQUE (room_id, room_type) ); CREATE TABLE standard_room ( room_id integer primary key, room_type VARCHAR not null default 'standard_room', CONSTRAINT roomid_std_roomtype_fk FOREIGN KEY (room_id, room_type) REFERENCES public."room" (room_id, room_type), CONSTRAINT std_room_constraint CHECK (room_type = 'standard_room') ); CREATE TABLE family_room ( room_id integer primary key, room_type VARCHAR not null default 'family_room', CONSTRAINT roomid_fam_roomtype_fk FOREIGN KEY (room_id, room_type) REFERENCES "room" (room_id, room_type), CONSTRAINT fam_room_constraint CHECK (room_type = 'family_room') ); NOTE: The SQL above uses constraints to enforce the child room_type values default to the parent tables' room_type values: 'standard_room' or 'family_room'.
PROBLEM: Since the child tables Primary Key's expect either the standard and family room Primary Key that means you can't insert more than one record in thsee two child tables.
insert into room (room_type) VALUES ('standard_room'); //Works insert into room (room_type) values ('family_room'); //Works insert into standard_room (room_id,pictureAttachment) VALUES (1,'Before Paint'); //Works insert into standard_room (room_id,pictureAttachment) VALUES (1,'After Paint'); //Fails insert into standard_room (room_id,pictureAttachment) VALUES (1,'With Furniture'); insert into family_room (room_id,pictureAttachment) VALUES (2, 'Beofre Kids'); //Works insert into family_room (room_id,pictureAttachment) VALUES (2,'With Kids'); //Fails To make the tables accept > 1 row you have to remove the Primary Keys from the 'standard_room' and 'family_room' tables which is BAD database design. Despite 26 upvotes I will ping OP about this as I can see the answer was typed free hand.
For smallish tables with less than a handful of variations a simple alterative is a single table with Bool columns for different table Primary Key fields.
Single Table "Room"
| Id | IsStandardRoom | IsFamilyRoom | Desc | Dimensions |
|---|---|---|---|---|
| 1 | True | False | Double Bed, BIR | 3 x 4 |
| 2 | False | True | 3 Set Lounge | 5.5 x 7 |
SELECT * FROM Room WHERE IsStdRoom = true; At the end of the day, in a relational database it's not very common to be adding Room Types when it involves creating the necessary related database tables using DDL commands (CREATE, ALTER, DROP).
A typical future proof database design allowing for more Tables would look something like this:
Multi Many-To-Many Table "Room"
| Id | TableName | TableId |
|---|---|---|
| 1 | Std | 8544 |
| 2 | Fam | 236 |
| 3 | Std | 4351 |
Either Standard or Family:
select * from standard_room sr where sr.room_id in (select TableId from room where TableName = 'Std'); select * from family_room fr where fr.room_id in (select id from room where TableName = 'Fam'); Or both:
select * from standard_room sr where sr.room_id in (select TableId from room where TableName = 'Std') UNION select * from family_room fr where fr.room_id in (select id from room where TableName = 'Fam'); Sample SQL to demo Polymorphic fields:
If you want to have different Data Types in the polymorphic foreign key fields then you can use this solution. Table r1 stores a TEXT column, r2 stores a TEXT[] Array column and r3 a POLYGON column:
CREATE OR REPLACE FUNCTION null_zero(anyelement) RETURNS INTEGER LANGUAGE SQL AS $$ SELECT CASE WHEN $1 IS NULL THEN 0 ELSE 1 END; $$; CREATE TABLE r1 ( r1_id SERIAL PRIMARY KEY , r1_text TEXT ); INSERT INTO r1 (r1_text) VALUES ('foo bar'); --TEXT CREATE TABLE r2 ( r2_id SERIAL PRIMARY KEY , r2_text_array TEXT[] ); INSERT INTO r2 (r2_text_array) VALUES ('{"baz","blurf"}'); --TEXT[] ARRAY CREATE TABLE r3 ( r3_id SERIAL PRIMARY KEY , r3_poly POLYGON ); INSERT INTO r3 (r3_poly) VALUES ( '((1,2),(3,4),(5,6),(7,8))' ); --POLYGON CREATE TABLE flex_key_shadow ( flex_key_shadow_id SERIAL PRIMARY KEY , r1_id INTEGER REFERENCES r1(r1_id) , r2_id INTEGER REFERENCES r2(r2_id) , r3_id INTEGER REFERENCES r3(r3_id) ); ALTER TABLE flex_key_shadow ADD CONSTRAINT only_one_r CHECK( null_zero(r1_id) + null_zero(r2_id) + null_zero(r3_id) = 1) ; CREATE VIEW flex_key AS SELECT flex_key_shadow_id as Id , CASE WHEN r1_id IS NOT NULL THEN 'r1' WHEN r2_id IS NOT NULL THEN 'r2' WHEN r3_id IS NOT NULL THEN 'r3' ELSE 'wtf?!?' END AS "TableName" , CASE WHEN r1_id IS NOT NULL THEN r1_id WHEN r2_id IS NOT NULL THEN r2_id WHEN r3_id IS NOT NULL THEN r3_id ELSE NULL END AS "TableId" FROM flex_key_shadow ; INSERT INTO public.flex_key_shadow (r1_id,r2_id,r3_id) VALUES (1,NULL,NULL), (NULL,1,NULL), (NULL,NULL,1); SELECT * FROM flex_key; 
