PostgreSQL - Multiple constraints-CodePudding

I want to add several CHECK CONSTRAINTS to a PostgreSQL 13 table. In natural language logic is : if a field contain a defined value, an other field must be filled. I have several scenarios to combine. When I add just one constraint it's ok, but when I want to accumulate them, CONSTRAINTS aren't respected and row can't be inserted.

Here is my table:

CREATE TABLE IF NOT EXISTS demo_table
(
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    id integer NOT NULL DEFAULT nextval('demo_table_id_seq'::regclass),        
    thematic character varying COLLATE pg_catalog."default",        
    field_a character varying COLLATE pg_catalog."default",        
    field_b character varying COLLATE pg_catalog."default",        
    CONSTRAINT demo_table_pkey PRIMARY KEY (uuid),
    CONSTRAINT field_a_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL),
    CONSTRAINT field_b_check CHECK (thematic::text ~~ 'B'::text AND field_b IS NOT NULL)
)

My expected logic is : when thematic like 'A', field_a can't be NULL or when thematic like 'B' field_b can't be NULL. With this settings I can't add rows because my CONSTRAINTS definitions never check both conditions (field_a IS NOT NULL and field_b IS NOT NULL).

I tried to define an unique CONSTRAINT as suggested in this post, but CHECK CONSTRAINT isn't respected either because parenthesis who isolate conditions aren't saved in the definition.

CREATE TABLE IF NOT EXISTS demo_table
(
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    id integer NOT NULL DEFAULT nextval('demo_table_id_seq'::regclass),        
    thematic character varying COLLATE pg_catalog."default",        
    field_a character varying COLLATE pg_catalog."default",        
    field_b character varying COLLATE pg_catalog."default",        
    CONSTRAINT demo_table_pkey PRIMARY KEY (uuid),
    CONSTRAINT field_a_b_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL OR thematic::text ~~ 'B'::text AND field_b IS NOT NULL)
)

How to combine multiple CONSTRAINTS like (IF ... ) OR (IF ... ) OR (IF ...) ?

CodePudding user response：

You can do like this.

alter table table_1 with check add 
   constraint ck_only_one check ((col1 is null and col2 is not null) or (col2 is null and col1 is not null));

Separation with parenthesis is to be given for better segregation.

CodePudding user response：

The problem with your approach is that your constraints are not full. For example:

CONSTRAINT field_a_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL),

The constraint says "the record is ok if thematic contains 'A' and field_a is not empty". That means the record is not OK otherwise (if it does not contain 'A'). If you appended your checks with "OK otherwise" you could have several of them - no problem:

CONSTRAINT field_a_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL OR NOT thematic::text ~~ 'A'::text)

As to why the parenthesis are removed - it's because they are not needed. The AND operator has priority over OR, so the expressions are the same with or without parenthesis.

You are welcome to check the solution at db<>fiddle