Re: Check/unique constraint question

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