Dear list,
To rule out any double combination of two identifiers, in any order, I applied the following constraint to a table:
CREATE TABLE test(
object_id INTEGER,
subject_id INTEGER,
CONSTRAINT "EXCL_double_combi" EXCLUDE USING btree (imm_LEAST(subject_id, object_id) WITH =, imm_GREATEST(subject_id, object_id) WITH =)
);
The imm_xxxxx functions are IMMUTABLE wrappers around LEAST() and GREATEST() respectively, in order to get the index to work...
pgsql Command "\d test" produces the following:
Table "public.test"
Column | Type | Modifiers
------------+---------+-----------
object_id | integer |
subject_id | integer |
Indexes:
"EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id, object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)
As I read it, an indexed in indeed being maintained, however it is not regarded a CONSTRAINT... Yet, the set-up works as like a charm :)) This online example shows a different summary:
Would this be an earlier / older version of PostgreSQL? I'm running 9.0.2, on Windows 7
By the way: has any one experience using EXCLUDE constraint inside Enterprise Architect? Since this is a pretty new database feature, it seems not yet supported by EA?
Looking forward to any of you sharing your experiences,
kind regards,
Rob
