Inheritance and foreign keys in Postgres

Question

This is about using inheritance and foreign keys in Postgresql databases.

Consider the following simplistic example whose structure is based on what I am building at the moment (but the specifics were contrived in realtime just for this question, so please excuse any shortcomings!):

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.

Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

Each table has a primary key set on the column ID.

The table Relationship has two foreign keys set on columns Partner1 and Partner2 which reference the table Person (column ID).

The (inherited) tables Gay and Lesbian also need to have foreign keys set on their Partner1 and Partner2 columns. The question is whether these foreign keys should reference the parent table Person, or whether they should reference (as appropriate) the child tables Man and Woman.

The questions comes up because, as stated in the manual for v9.6, section 5.9.1 :

Caveats

... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children ...... These deficiencies will probably be fixed in some future release ...

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

Of course, constraints can very well be imposed to achieve this, but it seems to me as though what I wrote above is a more elegant way of doing things. But I am also concerned about what the manual states towards the end - that the development team sees this as a problem, and might get rid of it. So I am also worried if my design would totally break after a future upgrade.

Any tips on the design above and suggestions for alternate ways would be most appreciated.

I would also be very grateful if there's someone from the Postgres dev team lurking around here, and is kind enough to comment.

Answer 1

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

I'm staunchly against the inheritance modeled being used by end users, but even here this isn't a valid use case of it. Your gender isn't a child table. It's an attribute on the table. The same can be said of your relationship.

Just add a column, gender and add another column relationship

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

I think is going to devolve into a political question but if people's gender can't change (horrible assumption) then the relationship is known between the two of them by the gender of the people. Why would you even want an attribute on the relationship table. The class gay or lesbian would be inferred from the genders of the participants. Yes if you put it there you can have an error where there is a lesbian relationship between two men, but that's an error introduced because your data should be inferred.

Which of these two seems more logical?

• f(p1,p2) = class of relationship
• f(p1,p2,relationship_class) = class of relationship

See also

• Using table inheritance instead of mapping tables