postgresql add constraint Check on 3 columns

Question

I have a table personnes

jouer=# create table personnes (
g_name VARCHAR ( 50 ),
f_name VARCHAR ( 50 ) UNIQUE,
company BOOLEAN NOT NULL DEFAULT false)
;

resulting in:


 Colonne |         Type          | Collationnement | NULL-able | Par défaut 
---------+-----------------------+-----------------+-----------+------------
 g_name  | character varying(50) |                 |           | 
 f_name  | character varying(50) |                 |           | 
 company | boolean               |                 | not null  | false

I want to add a constraint so that:

if company is true, g_name and f_name must be null and
if company is false, g_name and f_name are both required to be not null.

I have tried 2 things, but neither give the right result.

jouer=# ALTER TABLE personnes ADD CONSTRAINT personnes_company_check CHECK (
company is false
and g_name is not null
and f_name is not null)
;
ALTER TABLE

and

jouer=# ALTER TABLE personnes ADD CONSTRAINT personnes_company_check CHECK (
company is true 
and g_name is null
and f_name is null)
;

Bergi · Accepted Answer · 2022-10-16 19:36:58Z

2

There's various ways to write this. A literal translation of your requirement would be a conditional expression

ALTER TABLE personnes ADD CONSTRAINT personnes_company_check CHECK (
  CASE WHEN company
    THEN g_name IS NULL     AND f_name IS NULL
    ELSE g_name IS NOT NULL AND f_name IS NOT NULL
  END
);

but I'd prefer

ALTER TABLE personnes ADD CONSTRAINT personnes_company_check CHECK (
  company = (g_name IS NULL) AND
  company = (f_name IS NULL)
);

which you could also split into two separate constraints.

answered Oct 16, 2022 at 19:36

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

postgresql add constraint Check on 3 columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related