Subject Re: [ib-support] Foreign Key -- Alter Table fails -- could not find UNIQUE INDEX with specified columns
Author David K. Trudgett
On Wednesday 2002-03-27 at 14:40:25 +1100, Helen Borrie wrote:

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

Now that you say that, it rings a faint bell, and yes, that is the
problem. It's trying to create an FK to a column pair that is not the
foreign table's primary key.

I initially presumed that the script worked for Oracle, but have since
learned that that new part of the script is incomplete and hasn't been
actually run against Oracle yet. So I'm wondering whether it will in
fact work under Oracle.

BTW, did you know that Oracle forbids database comparisons in its
EULA? Unbelievable. Makes it look like they have something to hide,
doesn't it?

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

Yep. That's just the way it happened to be in the Oracle script.

Thanks for the info, Helen. It probably would only have twigged after
a few more days of bashing head against wall.

David Trudgett