Foreign key for inherited table in PostgreSQL

I have tables animals and water inherited from targets table. They look like this:

targets

create table targets
(
    id   integer not null
        primary key,
    name text
);

animals (water table is the same)

create table animals
(
    type_id  integer,
    flock_id integer,
    primary key (id)
)
    inherits (targets);

Also I have a actions table which has a foreign key target_id.

actions

create table actions
(
    id        integer not null
        primary key,
    target_id integer
        references targets(id),
);

I have some rows in animals and water tables. And I am trying to insert new row into actions table with target_id equals to id of some animal.

INSERT INTO actions (id, target_id)
VALUES (1, 4);

But I receive an error:

> [23503] ERROR: insert or update on table "actions" violates foreign key constraint "actions_target_id_fkey"
Key (target_id)=(4) is not present in table "targets".

Maybe I don't understand the table's inheritance, but how can I do my task correctly (target_id should be a foreign key for water and animals tables at the same time)?

I hope this might wrork , you have two tables, animals and water, that inherit from a parent table, targets. You want to create a foreign key in a different table that can reference both the animals and water tables using the id columnn of the targets table.

for that, you can create a foreign key that references the id column of the targets table and use a CHECK constraint to ensure that the referenced row is in either the animals or water table

CREATE TABLE some_table (
id serial primary key,
target_id integer references targets(id),
-- other columns
CONSTRAINT target_id_animals_water_check CHECK (
    target_id IN (SELECT id FROM animals)
    OR target_id IN (SELECT id FROM water)
)

);

some_table is the name of the table that you want to create the foreign key in, and target_id is the name of the column that will store the foreign key. The references clause specifies that the target_id column should reference the id column of the targets table.

The CHECK constraint ensures that the referenced row is in either the animals or water table. The IN operator is used to check if the target_id is in the set of id values in either the animals or water table.

With this setup, you can insert rows into the animals and water tables as well as the some_table table, and the foreign key constraint will ensure that only valid references are allowed and you can insert a row into the some_table table like this
INSERT INTO some_table (target_id) VALUES (1);