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.