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