Thread: Check/unique constraint question
I have a table with the following structure:
Column | Type | Modifiers
------------+---------+----------------------- active | boolean | not null default true id | integer | not
null
(other columns left out)
And would like to make a unique constraint which would only check the
uniqueness of id if active=true.
So, the following values would be acceptable:
('f',5)
('f',5)
('t',5)
But these would not be:
('t',5)
('t',5)
Basically, I want something like:
ALTER TABLE bar ADD CONSTRAINT foo UNIQUE(active (where active='t'),id)
But the above does not appear to exist. Is there a simple way to create a
check constraint for this type of situation, or do I need to create a function
to eval a check constraint?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
Unfortunately, at the moment Postgres doesn't support subqueries in
CHECK constraints, so it's seems that you should use trigger to check
what you need, smth like this:
CREATE OR REPLACE FUNCTION foo_check() RETURNS trigger AS $BODY$
BEGIN IF NEW.active = TRUE AND NEW.id IN ( SELECT id FROM foo WHERE active = TRUE AND id = NEW.id ) THEN
RAISE EXCEPTION 'Uniqueness violation on column id (%)', NEW.id; END IF;
RETURN NEW;
END
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER foo_check BEFORE INSERT OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE foo_check();
On 3/5/06, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> I have a table with the following structure:
>
> Column | Type | Modifiers
> ------------+---------+-----------------------
> active | boolean | not null default true
> id | integer | not null
> (other columns left out)
>
> And would like to make a unique constraint which would only check the
> uniqueness of id if active=true.
>
> So, the following values would be acceptable:
>
> ('f',5)
> ('f',5)
> ('t',5)
>
> But these would not be:
>
> ('t',5)
> ('t',5)
>
> Basically, I want something like:
> ALTER TABLE bar ADD CONSTRAINT foo UNIQUE(active (where active='t'),id)
>
> But the above does not appear to exist. Is there a simple way to create a
> check constraint for this type of situation, or do I need to create a function
> to eval a check constraint?
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Best regards,
Nikolay
just a better way (workaround for subqueries in check constraints...):
CREATE OR REPLACE FUNCTION id_is_valid( val INTEGER
) RETURNS boolean AS $BODY$
BEGIN IF val IN ( SELECT id FROM foo WHERE active = TRUE AND id = val ) THEN RETURN FALSE; ELSE
RETURNTRUE; END IF;
END
$BODY$ LANGUAGE plpgsql;
ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (active =
FALSE OR id_is_valid(id));
On 3/5/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> Unfortunately, at the moment Postgres doesn't support subqueries in
> CHECK constraints, so it's seems that you should use trigger to check
> what you need, smth like this:
>
> CREATE OR REPLACE FUNCTION foo_check() RETURNS trigger AS $BODY$
> BEGIN
> IF NEW.active = TRUE AND NEW.id IN (
> SELECT id FROM foo WHERE active = TRUE AND id = NEW.id
> ) THEN
> RAISE EXCEPTION 'Uniqueness violation on column id (%)', NEW.id;
> END IF;
>
> RETURN NEW;
> END
> $BODY$ LANGUAGE plpgsql;
>
> CREATE TRIGGER foo_check BEFORE INSERT OR UPDATE ON foo
> FOR EACH ROW EXECUTE PROCEDURE foo_check();
>
> On 3/5/06, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> > I have a table with the following structure:
> >
> > Column | Type | Modifiers
> > ------------+---------+-----------------------
> > active | boolean | not null default true
> > id | integer | not null
> > (other columns left out)
> >
> > And would like to make a unique constraint which would only check the
> > uniqueness of id if active=true.
> >
> > So, the following values would be acceptable:
> >
> > ('f',5)
> > ('f',5)
> > ('t',5)
> >
> > But these would not be:
> >
> > ('t',5)
> > ('t',5)
> >
> > Basically, I want something like:
> > ALTER TABLE bar ADD CONSTRAINT foo UNIQUE(active (where active='t'),id)
> >
> > But the above does not appear to exist. Is there a simple way to create a
> > check constraint for this type of situation, or do I need to create a function
> > to eval a check constraint?
> >
> > --
> > Jeff Frost, Owner <jeff@frostconsultingllc.com>
> > Frost Consulting, LLC http://www.frostconsultingllc.com/
> > Phone: 650-780-7908 FAX: 650-649-1954
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
> --
> Best regards,
> Nikolay
>
--
Best regards,
Nikolay
On Mar 05 12:02, Nikolay Samokhvalov wrote:
> Unfortunately, at the moment Postgres doesn't support subqueries in
> CHECK constraints
I don't know how feasible this is but, it's possible to hide subqueries
that will be used in constraints in procedures. Here's an alternative
method to Nikolay's:
CREATE TABLE where_check (active bool, id int);
CREATE OR REPLACE FUNCTION check_id (bool, int) RETURNS bool AS ' SELECT CASE WHEN $1 THEN NOT EXISTS (SELECT 1
FROM where_check AS W WHERE W.active IS TRUE AND W.id = $2) ELSE TRUE
END;
' LANGUAGE SQL;
-- A partial index like
-- CREATE INDEX active_id_idx ON where_check (id)
-- WHERE active IS TRUE;
-- should speed up above query
ALTER TABLE where_check ADD CONSTRAINT idchk CHECK (check_id(active, id));
test=# INSERT INTO where_check VALUES (TRUE, 2);
INSERT 0 1
test=# INSERT INTO where_check VALUES (FALSE, 2);
INSERT 0 1
test=# INSERT INTO where_check VALUES (TRUE, 2);
ERROR: new row for relation "where_check" violates check constraint
"idchk"
Regards.
On Mar 5, 2006, at 17:25 , Jeff Frost wrote: > And would like to make a unique constraint which would only check > the uniqueness of id if active=true. I believe you're looking for what is called a partial index. http://www.postgresql.org/docs/current/interactive/indexes-partial.html Note, I've added a foo_id column to make sure each row is unique. (Duplicates are a Bad Thing.) create table foo ( foo_id serial not null , id integer not null , active boolean not null ); create unique index foo_partial_idx on foo (id) where active; insert into foo (id, active) values (5, false); insert into foo (id, active) values (5, false); insert into foo (id, active) values (5, true); insert into foo (id, active) values (6, false); insert into foo (id, active) values (6, true); select * from foo; foo_id | id | active --------+----+-------- 1 | 5 | f 2 | 5 | f 3 | 5 | t 4 | 6 | f 5 | 6 | t (5 rows) insert into foo (id, active) values (5, true); ERROR: duplicate key violates unique constraint "foo_partial_idx" Michael Glaesemann grzm myrealbox com
On Sun, Mar 05, 2006 at 12:02:58PM +0300, Nikolay Samokhvalov wrote: > Unfortunately, at the moment Postgres doesn't support subqueries in > CHECK constraints, so it's seems that you should use trigger to check > what you need The OP could also use a check constraint with a function if everything (the context) but the value to check is known beforehand or can be derived from it. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, 5 Mar 2006, Michael Glaesemann wrote: > > On Mar 5, 2006, at 17:25 , Jeff Frost wrote: > > I believe you're looking for what is called a partial index. > > http://www.postgresql.org/docs/current/interactive/indexes-partial.html > > create unique index foo_partial_idx on foo (id) where active; Thanks Michael, that's exactly what I was looking for. Guess I just couldn't figure out how to search for it in the docs properly. Also thanks to Nikolay and Volkan for the other responses which were along the lines of what I thought was necessary. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
There is something a bit odd about this solution: If you start with an empty
table, the constraint will allow you to do
INSERT INTO foo (active, id) VALUES ('t', 5);
But if you insert this row into the table first and /then/ try to add the
constraint, it will complain that an existing row violates the constraint.
This begs the question of when constraints are checked.
I had always thought of constraints as being static conditions that (unlike
some trigger condition that masquerades as a constraint) apply equally to
existing rows and to rows you are about to add. This seems to show that not
all constraints work this way.
Nikolay Samokhvalov wrote
> just a better way (workaround for subqueries in check constraints...):
>
> CREATE OR REPLACE FUNCTION id_is_valid(
> val INTEGER
> ) RETURNS boolean AS $BODY$
> BEGIN
> IF val IN (
> SELECT id FROM foo WHERE active = TRUE AND id = val
> ) THEN
> RETURN FALSE;
> ELSE
> RETURN TRUE;
> END IF;
> END
> $BODY$ LANGUAGE plpgsql;
> ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (active =
> FALSE OR id_is_valid(id));
>
> ...
--
View this message in context: http://postgresql.nabble.com/Check-unique-constraint-question-tp2145289p5829778.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Scott Rohde <srohde@illinois.edu> writes:
> There is something a bit odd about this solution: If you start with an empty
> table, the constraint will allow you to do
> INSERT INTO foo (active, id) VALUES ('t', 5);
> But if you insert this row into the table first and /then/ try to add the
> constraint, it will complain that an existing row violates the constraint.
> This begs the question of when constraints are checked.
> I had always thought of constraints as being static conditions that (unlike
> some trigger condition that masquerades as a constraint) apply equally to
> existing rows and to rows you are about to add. This seems to show that not
> all constraints work this way.
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.
The original example seemed to work for retail inserts because the check
gets applied before the row is physically inserted. It would fail on
updates though, or when trying to add the constraint after the fact.
regards, tom lane
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.
Scott Rohde <srohde@illinois.edu> writes:
> 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."
They certainly don't work like that in Postgres, and I doubt in other
DBMSes either. A CHECK constraint is assumed to involve only the contents
of a single row, and it's checked for each row when (actually before) that
row is inserted or updated.
There is a thing in SQL called an "assertion" which has the sort of
unconstrained semantics you imagine. Postgres doesn't implement those,
and we're not alone. The cost of enforcing them is nigh prohibitive.
regards, tom lane