Hi,

> I use Firebird v. WI-T1.0.0.679 .
>
> 1. I create a unique index on tableA:
>
> create unique index foo_index on tableA (field1, field2)
>
> The index is built ok.
>
> 2. I try to create a reference on tableB:
>
> ALTER TABLE tableB add FOREIGN KEY (field1, field2) REFERENCES tableA
> (field1, field2)
>
> When I do so, I get the error message
>
> "could not find unique index with specified columns"
>
>
> When I do this using only 1 column, ev works ok.
>
> Besides, if I try to build a reference on a multi-column primary index, ev
> works ok.
>
> Any idea ?

Yes - the error message is somewhat misleading. A Foreign Key doesn't need
an unique index, it needs a Primary Key or Unique Constraint. Both are
enforced (implemented) via a system generated unique index. As a FK needs a
PK/Unique constraint, unique indices fail - indices aren't used to enforce
integrity (for example, uniqueness), but for lookup speed.

When using the Constraint Editor in InterBase Workbench, you will see that
you can easily create FKs by selecting source columns and a destination
constraint, not columns. In this way, you can quickly see if a FK is
possible.


Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."