PostgreSQL Custom not null constraint

Question

I want to add a NOT NULL contstraint to one of my table fields but only when another field has a certain value.

So I have a services table:

services:
 - id
 - professional_id
 - is_master

I want to write a SQL constraint that whenever is_master is false professional_id cannot be null. I've tried things like:

CREATE TABLE "services" (
    "id" text NOT NULL,
    "professional_id" text REFERENCES professionals ON DELETE CASCADE ON UPDATE CASCADE,
    EXCLUDE USING gist (
        professional_id WITH =,
    ) WHERE (NOT is_master),
    "is_master" boolean NOT NULL DEFAULT false,
    PRIMARY KEY ("id")
);

What is the correct way to write this SQL?

Create a trigger to achieve that postgresql.org/docs/9.1/static/sql-createtrigger.html — Romain B.
– Romain B., Commented Nov 9, 2017 at 15:52

Gordon Linoff · Accepted Answer · 2017-11-09 16:09:43Z

2

You can do this with a check constraint. A simple one is:

constraint chk_services_master_professions
    check (is_master or professional_id is not null)

Note: This version assumes that is_master is never NULL -- or that NULL is equivalent to "false".

edited Nov 9, 2017 at 16:09

answered Nov 9, 2017 at 16:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Rodrigo Over a year ago

This is def the cleanest one but I had already accepted the other answer before seeing this.

Erwin Brandstetter Over a year ago

@Rodrigo: Are you aware that you can switch the chosen answer?

Clodoaldo Neto · Accepted Answer · 2017-11-09 15:56:52Z

2

check (not is_master and professional_id is not null or is_master)

answered Nov 9, 2017 at 15:56

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2017-11-09 15:59:46Z

0

You could use CHECK constraint:

CREATE TABLE "services" (
    "id" text NOT NULL,
    "professional_id" text 
       CHECK (CASE WHEN "is_master" IS FALSE AND "professional_id" IS NULL 
              THEN FALSE ELSE TRUE END), -- and rest of FK
    "is_master" boolean NOT NULL DEFAULT false,
    PRIMARY KEY ("id")
);

Rextester Demo

INSERT INTO services
VALUES (1,1, FALSE);

INSERT INTO services
VALUES (2,1, true);

INSERT INTO services
VALUES (3, null, true);

INSERT INTO services
VALUES (4, null, false);

23514: new row for relation "services" violates check constraint "services_check"

edited Nov 9, 2017 at 15:59

answered Nov 9, 2017 at 15:54

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Custom not null constraint

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related