What is the best way to work with an optional FK constraint in Postgres?

Question

I have a table in Postgres which contains Things. Each of these Things can be of 1 of 3 types:

1. is a self contained Thing
2. is an instance of a SuperThingA
3. is an instance of a SuperThingB.

Essentially, in object terms, there's an AbstractThing, extended in different ways by SuperThingA or SuperThingB and all the records on the Thing table are either unextended or extended in 1 of the 2 ways.

In order to represent this on the Thing table, I have a number of fields which are common to Things of all types, and I have 2 optional FK reference columns into the SuperThingA and SuperThingB tables.

Ideally, I would like a "FK IF NOT NULL" constraint on each the two, but this doesn't appear to be possible; as far as I can see, all I can do is make both fields nullable and rely on the using code to maintain the FK relationships, which is pretty sucky. This seems to be doable in other databases, for example SQL Server, as per SQL Server 2005: Nullable Foreign Key Constraint, but not any way that I've found so far in PG

How else can I handle this - an insert/update trigger which checks the values when either of those fields is not null and checks the value is present on whichever parent table? That's maybe doable on a small parent table with limited inserts on the Thing table (which, in fairness, is largely the case here - no more than a couple of hundred records in each of the parent tables, and small numbers of inserts on the Thing table), but in a more general case, would be a performance black hole on the inserts if one or both parent table were large

This is not currently enforced with a FK relationship. I've reviewed the PG docs, and it seem pretty definitive that I can't have an optional FK relatioship (which is understandable). It leaves me with a table definition something like this:

CREATE TABLE IF NOT EXISTS Thing(
    Thing_id             int4           NOT NULL,
    Thing_description    varchar(40),
    Thing_SuperA_FK     int4,
    Thing_SuperB_FK         char(10),
    CONSTRAINT ThingPK PRIMARY KEY (Thing_id)
)
;

Answer 1

Every foreign key on a nullable column is only enforced when the value is non-null. This is default behavior.

create table a (
    id int4 not null primary key
);

create table b (
    id int4 not null primary key, 
    a_id int4 references a(id)
);

In the example table b has an optional reference to table a.

insert into a values(1); -- entry in table a
insert into b values (1, 1); -- entry in b with reference to a
insert into b values (2, null); -- entry in b with no reference to a

---

Depending on your use case it also might make sense to reverse your table structure. Instead of having the common table pointing to two more specialized tables you can have it the other way around. This way you avoid the non-null columns entirely.

create table common(
    id int4 primary key
    -- all the common fields
);

create table special1(
    common_id int4 not null references common(id)
    -- all the special fields of type special1
);

create table special2(
    common_id int4 not null references common(id)
    -- all the special fields of type special2
);

Answer 2

You need your SuperN_FK fields defined as nullable foreign keys, then you'll need check constraint(s) on the table to enforce the optional NULLability requirements.

CREATE TABLE Things
   (  ID int primary key
    , col1 varchar(1)
    , col2 varchar(1)

    , SuperA_FK int constraint fk_SuperA references Things(ID)
    , cola1 varchar(1)
    , constraint is_SuperA check ((SuperA_FK is null and cola1 is null) or 
                                  (SuperA_FK is not null and cola1 is not null))

    , SuperB_FK int constraint fk_SuperB references Things(ID)
    , colb1 varchar(1)
    , constraint is_SuberB check ((SuperB_FK is null and colb1 is null) or
                                  (SuperB_FK is not null))

    , constraint Super_Constraint check (
        case when SuperA_FK is not null then 1 else 0 end +
        case when SuperB_FK is not null then 1 else 0 end
        <= 1 )
   );

In the above example I've split the check constraints up for ease maintenance. The two is_SuperN constraints enforce the NULL requirements on the FK and it's related detail columns, either all NULLs or the FK is not null and some or all of the detail columns are not null. The final Super_Constraint ensures that at most one SuperN_FK is not null.