Subject Re: [ib-support] cannot build an integrity constraint
Author Helen Borrie
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.


All for Open and Open for All
Firebird Open SQL Database · ·