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