No. A foreign key constraint can only have a single target table.
Inheritance is no workaround. The manual:
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. This is true on
both the referencing and referenced sides of a foreign key constraint.
You could build a less strict replacement with triggers. Or with a NOT VALID check constraint using a fake IMMUTABLE function to check on INSERT only. See:
Neither enforces strict referential integrity. If you need that, you have to modify your table definition.
Split column
Ideally, a column should contain one kind of data to begin with. So this is the cleanest way:
CREATE TABLE collections_of_maps (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, title text NOT NULL
);
CREATE TABLE map_layers (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, parent_collection_id int
, parent_layer_id int
, name text NOT NULL
, CONSTRAINT map_layers_one_parent_chk CHECK (num_nulls(parent_collection_id, parent_layer_id) = 1)
, CONSTRAINT map_layers_parent_collection_id_fk FOREIGN KEY (parent_collection_id) REFERENCES collections_of_maps
, CONSTRAINT map_layers_parent_layer_id_fk FOREIGN KEY (parent_layer_id) REFERENCES map_layers
);
db<>fiddle here
About num_nulls():
Keep single column
If you must keep a single parent_id column:
CREATE TABLE collections_of_maps (
id int GENERATED BY DEFAULT AS IDENTITY
, parent_collection bool NOT NULL DEFAULT true CHECK (parent_collection) -- ! ③
, title text NOT NULL
, CONSTRAINT collections_of_maps_pkey PRIMARY KEY (id, parent_collection) -- ! ③
);
CREATE TABLE map_layers (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, parent_id int NOT NULL -- !
, parent_collection bool CHECK (parent_collection) -- ! ①
, parent_layer bool CHECK (parent_layer) -- !
, name text NOT NULL
, CONSTRAINT map_layers_one_parent CHECK (num_nulls(parent_collection, parent_layer) = 1) -- ! ②
, CONSTRAINT map_layers_for_fk_uni UNIQUE (id, parent_collection) -- !
, CONSTRAINT map_layers_parent_collection_id_fk FOREIGN KEY (parent_id, parent_collection) REFERENCES collections_of_maps (id, parent_collection)
, CONSTRAINT map_layers_parent_layer_id_fk FOREIGN KEY (parent_id, parent_layer) REFERENCES map_layers (id, parent_collection)
);
db<>fiddle here
I added two boolean flags parent_collection and parent_layer to mark the type of parent. CHECK constraints enforce that one of both flags must be null, and the other one true.
① Simple CHECK enforces true (or null).
② Enforces exactly one of both null.
Related:
The default MATCH SIMPLE behavior of multicolumn FK constraints does not enforce the FK if one of the columns is NULL. See:
Thus, only the right one of the two FK constraints is enforced for each row.
③ We need to add the logically redundant flag parent_collection and include it in a UNIQUE or PK constraint to allow the multicolumn FK reference. I appropriated the PK for the purpose. Alternatively (if you need the plain PK) add a (redundant) UNIQUE constraint.
The way I implemented it, self-references cannot be nested. I.e., rows in map_layers can only reference rows in map_layers that reference collections_of_maps. See example in the fiddle. Can be adapted ...
