Re: The exact timing at which CHECK constraints are checked

On Thu, Dec 15, 2011 at 3:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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,