Populating parent table from multiple child tables with compound keys

Question

I'm currently experimenting a use case where I have several child tables (here two for simplicity's sake) that must be referenced by their "type + id" in a unique parent table, and tested some design variations, e.g. by setting a primary key on two columns on the parent table (entity) but without any success.

Considering the following PostgreSQL statements:

-- DROP TABLE IF EXISTS public.entity;
CREATE TABLE IF NOT EXISTS public.entity
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    --id INT NOT NULL UNIQUE,
    entity_type TEXT NOT NULL,
    created_at TIMESTAMPZ DEFAULT NOW(),
    UNIQUE (id, entity_type),--
    --PRIMARY KEY (entity_type, id)
    CONSTRAINT entity_type_chk
        CHECK (entity_type IN ('tree', 'pond'))
);

-- DROP TABLE IF EXISTS public.tree
CREATE TABLE IF NOT EXISTS public.tree
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    entity_type TEXT NOT NULL DEFAULT 'tree',
    entity_id INT REFERENCES public.entity(id),
    species TEXT,
    diameter INT,
    CONSTRAINT tree_entity_type_id_fkey FOREIGN KEY (entity_type, id)
        REFERENCES public.entity (entity_type, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

-- DROP TABLE IF EXISTS public.pond
CREATE TABLE IF NOT EXISTS public.pond
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    entity_type TEXT NOT NULL DEFAULT 'pond',
    entity_id INT REFERENCES public.entity(id),
    common_name TEXT,
    surface INT,
    CONSTRAINT pond_entity_type_id_fkey FOREIGN KEY (entity_type, id)
        REFERENCES public.entity (entity_type, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION

);

-- INSERT statements don't work
INSERT INTO public.tree(species, diameter) VALUES
  ('quercus ilex', 60),
  ('fagus sylvatica', 55),
  ('acer pseudoplatanus', 70);

INSERT INTO public.pond(common_name, surface) VALUES
  ('Blue pond', 60),
  ('Royal beauty', 55),
  ('Prinz Hertzpold', 70);

when executing the INSERT statements, I encounter the following error:

ERROR:  Key (entity_type, id)=(tree, 1) is not present in table "entity".
  insert or update on table "tree" violates foreign key constraint "tree_entity_type_id_fkey" 

ERROR:  insert or update on table "tree" violates foreign key constraint "tree_entity_type_id_fkey"
SQL state: 23503
Detail: Key (entity_type, id)=(tree, 1) is not present in table "entity".

How could I automatically populate the parent table (entity) when inserting children elements?

I tried to use a trigger:

BEGIN;

CREATE OR REPLACE FUNCTION public.populate_parent()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  INSERT INTO public.entity (entity_type, id)
  VALUES(NEW.entity_type, NEW.id);
  RETURN NEW;
END;
$$;

CREATE TRIGGER populate_parent_bit
  BEFORE INSERT
  ON public.tree
  FOR EACH ROW
  EXECUTE PROCEDURE public.populate_parent();

CREATE TRIGGER populate_parent_bit
  BEFORE INSERT
  ON public.pond
  FOR EACH ROW
  EXECUTE PROCEDURE public.populate_parent();

COMMIT;

But then the first INSERT query raises:

ERROR:  Column "id" is an identity column defined as GENERATED ALWAYS.
  cannot insert a non-DEFAULT value into column "id" 

ERROR:  cannot insert a non-DEFAULT value into column "id"
SQL state: 428C9
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
Context: SQL statement "INSERT INTO public.entity (entity_type, id)
  VALUES(NEW.entity_type, NEW.id)"
PL/pgSQL function feed_entity() line 3 at SQL statement

Maybe the parent-child relation must go the other way but then I don't know how to set up a FK in the table entity pointing to several tables (this actually doesn't make sense to me).

---

Version

PostgreSQL 16.0 (Debian 16.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Answer 1

A Foreign Key reference presumes that it links to an existing entity. The inserts before the query fails because the entity does not exist.

The trigger is trying to assert the value of the IDENTITY column of the entity table; you clearly cannot do that.

Instead, in the trigger function you need to insert a new row into the entity table, and in the INSERT, use the RETURNING keyword to get the new parent ID back, and set that value in the tree record you are inserting, in the trigger.

ALSO - your FK definitions are incorrect, insofar as the constraint on the tree/pond tables should be on entity_id and not id.

This gives me the following table definitions:

CREATE TABLE IF NOT EXISTS public.entity
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    --id INT NOT NULL UNIQUE,
    entity_type TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (id, entity_type),--
    --PRIMARY KEY (entity_type, id)
    CONSTRAINT entity_type_chk
        CHECK (entity_type IN ('tree', 'pond'))
);

DROP TABLE IF EXISTS public.tree;
CREATE TABLE IF NOT EXISTS public.tree
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    entity_type TEXT NOT NULL DEFAULT 'tree',
    entity_id INT REFERENCES public.entity(id),
    species TEXT,
    diameter INT,
    CONSTRAINT tree_entity_type_id_fkey FOREIGN KEY (entity_type, entity_id)
        REFERENCES public.entity (entity_type, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

DROP TABLE IF EXISTS public.pond;
CREATE TABLE IF NOT EXISTS public.pond
(
    id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    entity_type TEXT NOT NULL DEFAULT 'pond',
    entity_id INT REFERENCES public.entity(id),
    common_name TEXT,
    surface INT,
    CONSTRAINT pond_entity_type_id_fkey FOREIGN KEY (entity_type, entity_id)
        REFERENCES public.entity (entity_type, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION);

and I updated your trigger to create the entity, as described above:

CREATE OR REPLACE FUNCTION public.populate_parent()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
DECLARE new_entity_id INTEGER;
BEGIN
  INSERT INTO public.entity (entity_type) VALUES(NEW.entity_type) RETURNING id INTO new_entity_id;
  NEW.entity_id = new_entity_id;
  RETURN NEW;
END;
$$;

And now:

INSERT INTO public.tree(species, diameter) VALUES
  ('quercus ilex', 60),
  ('fagus sylvatica', 55),
  ('acer pseudoplatanus', 70);

produces:

INSERT 0 3

Voila!