In PostgreSQL, I need a constraint to control if a value(s) is/are exists. This constraint is applied in an uuid[] data type column. It must check if each values of the array exists in an other column of the same table, in a uuid data type column.

I'm able to write a constraint with two similar data types, but how to run the same logic with two differents data types ?

In my case: check values in an uuid[] array with an uuid type.

Table

CREATE TABLE IF NOT EXISTS public.contact
(
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),    
    name character varying COLLATE pg_catalog."default" NOT NULL,    
    parent_uuid uuid,
    parent_name character varying COLLATE pg_catalog."default",
    child_uuids uuid[],
    child_names character varying[],
    CONSTRAINT contact_pk PRIMARY KEY (uuid),
    CONSTRAINT name_uk UNIQUE (name)       
)

TABLESPACE pg_default;

First constraint (parent): Ok

-- Check if parent_uuid exists in public.contact
ALTER TABLE public.contact ADD CONSTRAINT parent_uuid_fk FOREIGN KEY (parent_uuid)
REFERENCES public.contact (uuid) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;

Second constraint (children): Incompatible types: uuid[] and uuid

-- Check if child_uuids exists in public.contact
ALTER TABLE public.contact ADD CONSTRAINT child_uuids_fk FOREIGN KEY (child_uuids)
REFERENCES public.contact (uuid) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;

CodePudding user response：

There is no good way to do that.

• You cannot create a constraint like that.

• What you can do is to create a trigger function that tries to verify the condition and throws an error if it is violated. But such triggers are always susceptible to race conditions unless you use the SERIALIZABLE transaction isolation level, which incurs a performance penalty.

I would say that the only good solution here is to redesign and use a junction table to implement the relationship rather than an array. Then the problem would go away.