PostgreSQL check constraint

Question

CREATE TABLE my_table
(
    fk      INTEGER,
    field_1 INTEGER,
    field_2 INTEGER,
    field_3 INTEGER
)

VALID:

    fk    |    field_1    |    field_2    |    field_3              
----------+---------------+---------------+---------------
     1    |      1        |      null     |      null
     1    |      null     |      1        |      null
     1    |      null     |      null     |      1

It's possible to create check constraint which allows only 1 field of 3 can be not null for 1 fk ?

Yes, but that might not be the best idea.

Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall'

2012-04-05 11:03:33 +00:00
Commented Apr 5, 2012 at 11:03 — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Apr 5, 2012 at 11:03

Nitram · Accepted Answer · 2012-04-05 10:55:40Z

2

The straight forward way come to mind:

CHECK ((field_1 IS NOT NULL AND field_2 IS NULL AND field_3 IS NULL) OR
       (field_2 IS NOT NULL AND field_1 IS NULL AND field_3 IS NULL) OR
       (field_3 IS NOT NULL AND field_1 IS NULL AND field_2 IS NULL))

answered Apr 5, 2012 at 10:55

Nitram

6,7462 gold badges26 silver badges32 bronze badges

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

Comments

vyegorov · Accepted Answer · 2012-04-05 20:56:46Z

It is not quite clear what you'd like to achieve.

If you need only one column being NOT NULL per row, then Nitram's answer will do, you can also try:

CHECK ((sign(coalesce(field_1,0)) +
        sign(coalesce(field_2,0)) + sign(coalesce(field_3,0))) <= 1)

Otherwise, if you need to have only single NOT NULL column per all rows with the given FK, you should look into the CONSTRAINT TRIGGER, something like this:

CREATE OR REPLACE FUNCTION only_one() RETURNS trigger AS $only_one$
DECLARE
    cnt  int4;
BEGIN
    SELECT sign(coalesce(field_1,0)) +
           sign(coalesce(field_2,0)) +
           sign(coalesce(field_3,0)) +
           sign(coalesce(NEW.field_1,0)) +
           sign(coalesce(NEW.field_2,0))+
           sign(coalesce(NEW.field_3,0))
      INTO cnt
      FROM my_table WHERE fk = NEW.fk;

    IF cnt > 1 THEN
        RAISE EXCEPTION 'Sorry, too much NOT NULL values for FK=%', NEW.fk;
    END IF;

    RETURN NEW;
END;
$only_one$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER my_table_only_one 
 AFTER INSERT OR UPDATE ON my_table
   FOR EACH ROW EXECUTE PROCEDURE only_one();

Collectives™ on Stack Overflow

PostgreSQL check constraint

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related