Re: Multicolumn foreign keys need useless unique indices?

On Fri, 13 Sep 2002, Antti Haapala wrote:

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

SQL 92 would want you to normalize and remove bref from referencer
since it's redundant.  You're storing a reference to a table and
some of the dependent values to that reference in another table.
That's probably the best workaround, although I assume your real
case is more complicated.