postgresql add constraint Check on 3 columns-CodePudding

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)
;

CodePudding user response：

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.