1

I have 2 tables.

First table is parent

COLLECTIONS_OF_MAPS

id title
50013 Geological Map Series
50014 Climate Map Series

Second table is a child (but whilst describing map layers, it also has entries that are more like group layers, like parents to child maps within the same table, so there is a recursion element here) - this is then realised in a treeview control in software, just for context purposes.

MAP_LAYERS

id parent_layer_id name
1233 50013 Hillshade
1228 50013 Cultural Data
1231 50013 Rock Types
1235 1233 Offshore Hillshade
1234 1233 Onshore Hillshade
1229 1228 World Cities
1230 1228 World Countries
1245 1228 World Rivers
1270 1231 Sedimentary Rock
1271 1231 Igneous Rock
1272 1231 Metamorphic Rock

I have just shown an example for the first map collection - "Geological Map Series".

As you can see it the MAP_LAYERS parent_layer_id column references entries from BOTH tables; it's parent and itself. Can I create a foreign key rule to that references the ids of itself and or its parent table?

1 Answer 1

2

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 ...

3
  • Thanks for your time in putting together such a comprehensive answer. I think ultimately my table design is wrong, so I may have to change it so I have proper referential integrity.
    – Vidar
    Aug 22, 2022 at 14:19
  • Is the NumNulls just checking there can only be one out of the two columns with a NULL value?
    – Vidar
    Aug 22, 2022 at 14:51
  • @Vidar: numnulls() simply counts the number of NULL values in the input. It's a VARIADIC function taking up to 100 arguments. It's all in the linked answer. Aug 22, 2022 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.