Re: The exact timing at which CHECK constraints are checked

On Thu, Dec 15, 2011 at 3:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> They're checked at the instant that a row is inserted or updated.
> If you really need a deferred check, you'll need to build it yourself
> using an AFTER ROW trigger.

Thank you for taking time to answer my novice question.
The AFTER ROW trigger version is what I expected.

    CREATE TABLE foos (
      bar INTEGER
    );

    CREATE FUNCTION check_foo_cardinality() RETURNS trigger AS $$
      BEGIN
        IF (SELECT count(*) FROM foos) < 1 THEN
          RAISE EXCEPTION 'at least one row required';
        END IF;
        RETURN NULL;
      END
    $$ LANGUAGE plpgsql;

    CREATE CONSTRAINT TRIGGER check_cardinality
      AFTER DELETE ON foos
      INITIALLY DEFERRED
      FOR EACH ROW
      EXECUTE PROCEDURE check_foo_cardinality();

    INSERT INTO foos VALUES (1);
    DELETE FROM foos;  -- => ERROR:  at least one row required

Best,