Nice article, something I’ve had to implement several times before. I usually go for the “inverted” key (method 4) or the “multiple foreign keys” (method 1).
Typically I don’t include an explicit type field when going for method 1, I would just add a constraint that states only one foreign key may be filled in a la:
ALTER TABLE foo ADD CONSTRAINT only_one_fk
CHECK cardinality(array_remove(ARRAY[(fk1 IS NULL), (fk2 IS NULL), (fk3 IS NULL)], true)) = 1
I usually use a variant on method 4: put both the parent table’s primary key and the type ID in each child table, make (ID, type) in each child table a compound foreign key pointing to (ID, type) in the parent, and either add a check constraint to each child table to force the type ID to be the correct value for that table or use GENERATED ALWAYS to hardwire the value of the type ID. This fixes the problem the article points out where you can have multiple child tables pointing at the same parent, at the cost of a tiny amount of extra space per row. Once PostgreSQL adds support for non-stored generated columns, maybe the space cost will go away.
That’s a downside to foreign keys like this in general, isn’t it? Even if you’re using a “regular”/non-polymorphic relation you still have that possibility. Perhaps that’s why they didn’t mention it as a drawback specific to this technique.
It’s a general property of foreign keys, indeed. But it’s a drawback compared to the other methods which is why I would have called it out!
Having written the above, I just realized it also applies to method 4, of course, only the other way around, i.e. you could end up with orphan parents.
Nice article, something I’ve had to implement several times before. I usually go for the “inverted” key (method 4) or the “multiple foreign keys” (method 1).
Typically I don’t include an explicit type field when going for method 1, I would just add a constraint that states only one foreign key may be filled in a la:
I usually use a variant on method 4: put both the parent table’s primary key and the type ID in each child table, make (ID, type) in each child table a compound foreign key pointing to (ID, type) in the parent, and either add a check constraint to each child table to force the type ID to be the correct value for that table or use
GENERATED ALWAYSto hardwire the value of the type ID. This fixes the problem the article points out where you can have multiple child tables pointing at the same parent, at the cost of a tiny amount of extra space per row. Once PostgreSQL adds support for non-stored generated columns, maybe the space cost will go away.That’s really clever! Thanks for the tip, I will definitely use this strategy when I need to use the inverted key method.
Nice article.
These days I’m always thinking: we need first-class support for sum types in our data stores.
They forgot to mention a downside of method 1: You can end up with orphan records in the child tables.
That’s a downside to foreign keys like this in general, isn’t it? Even if you’re using a “regular”/non-polymorphic relation you still have that possibility. Perhaps that’s why they didn’t mention it as a drawback specific to this technique.
It’s a general property of foreign keys, indeed. But it’s a drawback compared to the other methods which is why I would have called it out!
Having written the above, I just realized it also applies to method 4, of course, only the other way around, i.e. you could end up with orphan parents.