CODEWITHSUNDEEP
sqlpostgresql
puk
puk

Reputation: 16802

PSQL Constraint based on column value

Is it possible to have a Constraint but only when one column is set to a particular value. For example take this pseudo-code example of a President which checks to make sure there is never more than 1 President at any time (note, this is NOT valid psql syntax)

CREATE TABLE president (
    id BIGSERIAL PRIMARY KEY,
    current BOOLEAN NOT NULL,
    CONSTRAINT there_can_be_only_one CHECK(COUNT(current=true)<=1)
);

Upvotes: 1

Views: 232

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

You can use the so called partial index to enforce this specific constraint. In SQL Server they are called filtered indexes.

CREATE UNIQUE INDEX IX ON president (current)
WHERE current = true;

This index should prevent having more than one row in a table with current value set to true, because it is defined as unique.

Upvotes: 3

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Unfortunately NO as far as I know and anyway it already tells us,

ERROR: aggregate functions are not allowed in check constraints.

But we can use BEFORE trigger to check that the data you are trying to insert should meets the criteria COUNT(current=true)<=1

CREATE TABLE president (
    id BIGSERIAL PRIMARY KEY,
    current BOOLEAN NOT NULL
);
---------------------------------------------------------------------
CREATE FUNCTION check_current_flag() 
RETURNS trigger 
AS $current_president$
DECLARE
  current_flag_count integer;
BEGIN
  SELECT COUNT(*) FILTER (WHERE current = true )
    INTO current_flag_count 
    FROM president;
 
 IF    new.current = true
   and current_flag_count >= 1 THEN
   RAISE EXCEPTION 'There can be only one current president';
   -- RETURN NULL;
 ELSE
  RETURN NEW;
 END IF;
END;
$current_president$ LANGUAGE plpgsql;
---------------------------------------------------------------------
CREATE TRIGGER current_president BEFORE INSERT OR UPDATE ON president
    FOR EACH ROW EXECUTE PROCEDURE check_current_flag();

Db<>Fiddle for reference

Note: You can either throw exception in case of preconditions doesn't match ore simply returning NULL will skip the insert and do nothing. as official document says also here

Upvotes: 1

Related Questions