At 01:24 PM 27-03-02 +1100, you wrote:

>I'm doing an automatic conversion of a 13000 line Oracle SQL script
>into SQL that IB/FB understands. Everything was working fine until
>recently, when a change made to the Oracle script was made.
>
>The FB version of the script generates a metadata update error that, on
>the face of it, seems to be misleading, because what it says is not
>true.
>
>The problem happens with this bit of SQL:
>
> ALTER TABLE TABLE1
> ADD CONSTRAINT FK_TAB1TAB2
> FOREIGN KEY (COL1, COL2)
> REFERENCES TABLE2 (COL1, COL2);
>
>The error:
>
> Statement failed, SQLCODE = -607
>
> unsuccessful metadata update
> -could not find UNIQUE INDEX with specified columns
>
>There is definitely a unique index over just those two columns in both
>of the tables. Inspection of the metadata using Marathon proves that.
>
>Does anyone have an idea what might be causing this error? It's not a
>typo in the column names -- I've checked that about four times already
>:-).
>
>I'm using Firebird 1.0 CS on Linux

David,
This is reminiscent of an old thread that circulated around the old Mers
list more than once, wrt to IB 5.x. AFAIK, the error message is
misleading, insofar as it implies that you can form a foreign key that
refers to a unique index. I believe in FB/IB, this is not the whole
story. A foreign key has to refer the primary key of the reference table
(which, by definition, has a unique index...) It should be telling you
"could not find PRIMARY KEY with specified columns".

btw, there is no requirement for the index on a foreign key to be unique.

cheers,
Helen

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