Re: [SQL] Check/unique constraint question

[Volkan YAZICI]

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly