CODEWITHSUNDEEP
sqlpostgresql
Rodrigo
Rodrigo

Reputation: 3278

PostgreSQL Custom not null constraint

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?

Upvotes: 2

Views: 83

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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".

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

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"

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: 2

Related Questions