From: | "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, "Jeff Frost" <jeff(at)frostconsultingllc(dot)com> |
Subject: | Re: Check/unique constraint question |
Date: | 2006-03-05 09:02:58 |
Message-ID: | e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)frostconsultingllc(dot)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(at)frostconsultingllc(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolay Samokhvalov | 2006-03-05 09:49:24 | Re: Check/unique constraint question |
Previous Message | Jeff Frost | 2006-03-05 08:25:58 | Check/unique constraint question |