CODEWITHSUNDEEP
postgresql
thiebo
thiebo

Reputation: 1435

postgresql add constraint Check on 3 columns

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:

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

Upvotes: 0

Views: 69

Answers (1)

Bergi
Bergi

Reputation: 664307

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.

Upvotes: 2

Related Questions