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-15 14:51:42Z

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, 2021 at 14:51

answered Sep 15, 2021 at 10:41

Julius Tuskenis

1,63010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GeoGyro Over a year ago

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.

Julius Tuskenis Over a year ago

What error message do you get? See dbfiddle.uk/… insert into demo_table(thematic, field_a) VALUES ('A', 'field_a'); suceeds

GeoGyro Over a year ago

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)

Julius Tuskenis Over a year ago

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 ?

GeoGyro Over a year ago

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 · Accepted Answer · 2021-09-18 11:08:21Z

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, 2021 at 11:08

GeoGyro

57918 silver badges38 bronze badges

answered Sep 15, 2021 at 11:08

N K Shukla

3621 gold badge4 silver badges14 bronze badges

1 Comment

GeoGyro Over a year ago

Parenthesis are the problem : if I execute this, they aren't saved when I check CONSTRAINT properties. Make a try, you will see.

Collectives™ on Stack Overflow

PostgreSQL - Multiple constraints

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related