'PostgreSQL - Constraint with differents data types
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;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
