At 11:04 AM 07-05-02 +0200, Duilio Foschi wrote:

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

The error message is misleading. What it cannot find is a unique
CONSTRAINT on those columns. A foreign key must be made to an integrity
constraint, not to an index.

If you want (field1, field2) to be referenced as a key, drop the unique
index and then create a unique constraint for the columns. A unique index
will be created automatically to support the constraint. But the reverse
is not true.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________