> > AFAIK, the extra index only slows down my inserts - it basically contains > > no usable information... > > Not 100% true. It will speed up cascade delete and update...
To clarify things: CREATE TABLE original ( a int PRIMARY KEY, b int ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "original" not found CREATE TABLE original ( a int PRIMARY KEY, b int, UNIQUE (a,b) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'original_pkey' for table 'original' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'original_a_key' for table 'original' CREATE CREATE TABLE referencer ( aref int, bref int, FOREIGN KEY (aref, bref) REFERENCES original(a, b) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE ilmo=# \d original Table "original" Column | Type | Modifiers --------+---------+----------- a | integer | not null b | integer | Primary key: a_pkey Unique keys: a_a_key Triggers: RI_ConstraintTrigger_41250, RI_ConstraintTrigger_41252 ilmo=# \d referencer Table "referencer" Column | Type | Modifiers --------+---------+----------- aref | integer | bref | integer | Triggers: RI_ConstraintTrigger_41248 Actually nothing changes. The unique constraint doesn't add anything new - it allows NULLs in column b and requires that combination (a, b) is unique... and it definitely is because column 'a' is unique (primary key). It just creates a multicol index and adds an useless extra constraint check, while almost the same data is available in index "original_a_pkey". -- Antti Haapala ---------------------------(end of broadcast)--------------------------- TIP 3: 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