Tom Lane-2 wrote
> ...
> Indeed, this illustrates perfectly why subqueries in CHECK constraints
> are generally a Bad Idea: the constraint is no longer just about the
> contents of one row but about its relationship to other rows, and that
> makes the timing of checks relevant. Hiding the subquery in a function
> doesn't do anything to resolve that fundamental issue.
> ...
I don't think subqueries in CHECK constraints are a bad idea /per se/--to my
mind it would depend on how they actually work. I don't know enough about
the SQL standard or about products that support them to know if they work
the way I /think/ they should work, which is basically this: "Guarantee that
condition X (written as a constraint on table Y) is satisfied by the
database when (1) the constraint is first added, and (2) whenever a change
is made to one or more rows of table Y."
In our example, if the function in the CHECK constraint was run after
provisionally changing the foo table, and if the changes were rolled back if
and only if the check failed, then it seems there would be no problem. It
may well be that bona fide CHECK subqueries (as opposed to procedural
functions run by the CHECK constraint) /do/ work this way.
In PostgreSQL, the best approximation I could come up with was a combination
of the TRIGGER function mentioned in Nikolay's first post together with this
CHECK:
CREATE OR REPLACE FUNCTION id_is_valid( val INTEGER
) RETURNS boolean AS $BODY$ DECLARE id_is_unique boolean;
BEGIN SELECT COUNT(*) <= 1 FROM foo WHERE active = TRUE AND id = val INTO
id_is_unique; RETURN id_is_unique;
END
$BODY$ LANGUAGE plpgsql;
ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (id_is_valid(id));
To summarize, any constraint should be fine as long as (1) it is always run
when any change is made to the database that might affect its value; (2) it
is run on the (provisional) /result/ state of the database.
--
View this message in context: http://postgresql.nabble.com/Check-unique-constraint-question-tp2145289p5829820.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.