Re: Multicolumn foreign keys need useless unique indices?

> > 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
ONDELETE 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
ONDELETE 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 nullb      | 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