From: | Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> |
---|---|
To: | Nikolay Samokhvalov <nikolay(at)samokhvalov(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org, Jeff Frost <jeff(at)frostconsultingllc(dot)com> |
Subject: | Re: Check/unique constraint question |
Date: | 2006-03-05 10:05:27 |
Message-ID: | 20060305100526.GA214@alamut |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2006-03-05 10:29:42 | Re: Check/unique constraint question |
Previous Message | Nikolay Samokhvalov | 2006-03-05 09:49:24 | Re: Check/unique constraint question |