PostgreSQL - Multiple constraints

Question

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 ...) ?

Julius Tuskenis · Accepted Answer · 2021-09-15T14:51:42.847

2

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

edited Sep 15 '21 at 14:51

answered Sep 15 '21 at 10:41

Julius Tuskenis

1,323
8
13

Ok with your example and logic but when I execute this I still have a violation of `CHECK CONSTRAINT` when I try to `INSERT` `thematic = 'A'` and something in `field_a`. – GeoGyro Sep 15 '21 at 12:16
What error message do you get? See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5ed5ec01e27d5bd328d915887bd12c70 `insert into demo_table(thematic, field_a) VALUES ('A', 'field_a');` suceeds – Julius Tuskenis Sep 15 '21 at 14:50
With your `CONSTRAINT` definition and this `INSERT` I have this message ` `ERROR: new row for relation "demo_table" violates check constraint "field_a_check" DETAIL: Failing row contains (3a9cad02-5733-4abd-8998-a1052e6c353e, 32, A, null)` – GeoGyro Sep 15 '21 at 15:56
So the constraint works? You try to insert a record with thematic = A and field_a =NULL and get a constraint violation. What is interesting the record '(3a9cad02-5733-4abd-8998-a1052e6c353e, 32, A, null)' does not contain a value for field_b - did you remove it ? – Julius Tuskenis Sep 15 '21 at 18:08
Something strange : with same version of `PostgreSQL`, table definition, `CONTRAINT` and `INSERT` it doesn't work on my side but it's ok on dbfiddle. I'm looking for possible differences and erros and give a feedback. – GeoGyro Sep 16 '21 at 07:14
I understand the difference, my `CONSTRAINT` definition wasn't exactly the same as yours, last part is important `... OR NOT thematic::text ~~ 'A'::text`. – GeoGyro Sep 22 '21 at 07:20

score 1 · Answer 2 · edited Sep 18 '21 at 11:08

1

You can do like this.

alter table table_1 
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.

edited Sep 18 '21 at 11:08

GeoGyro

487
12
32

answered Sep 15 '21 at 11:08

N K Shukla

288
1
3
12

Parenthesis are the problem : if I execute this, they aren't saved when I check `CONSTRAINT` properties. Make a try, you will see. – GeoGyro Sep 15 '21 at 12:01

PostgreSQL - Multiple constraints

2 Answers2