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