"polymorphism" for FOREIGN KEY constraints

Question

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?

Version

Answer 1

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.

Segmentation

The pattern you have been using is a good one. It's described by Martin Fowler as "Class Table Inheritance". You can see a summary on the web: martinfowler.com/eaaCatalog/classTableInheritance.html. As Fowler suggests, this Q is about inheritance more than polymorphism. Also, You have made use of the "shared primary key" technique for tying foreign keys and subclass primary keys together. Godd for +1. – Helse

It feels awkward needing 2 records inserted to represent each logical object. – Ebberta

Wouldn't it be better to just have a room_id in both subtypes which reference the supertype and just remove the room_type attribute? The room_type attribute is already present in the table name (or you could UNION those tables and create a seperate column: SELECT 'standard_room' AS "room_type" – Pilsudski

@Segmentation I ran into problems that I've pointed out in my answer. Any chance you could clarify exactly how your solution is supposed to work? – Sofko

Answer 2

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.

---

Alternate Solutions

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;

Sofko

Coming late to the party, but w.r.t. this statement: "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 these two child tables.", isn't that the point? Why would I want more than 1 row for each unique entry in the rooms table? – Gabby