Hi Geoff,

I found the following in my mails archive.

HTH,
Markus

>>>--------------------------------------

Date: Wed, 10 May 2000 10:04:20 -0400
From: Ann Harrison <aharrison@...>
Subject: Re: Error on foreign key creation
In-Reply-To: <20000510030354.78923.qmail@...>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 11:01 PM 5/9/00 -0400, Alexander Skaburskis wrote:

>I am getting the following error when I try to create a foreign key:
>
>unsuccessful metadata update
>-STORE RDB$REF_CONSTRAINTS failed
>-action cancelled by trigger (1) to preserve data integrity
>-Name of Referential Constraint not defined in constraints table.

The trigger referenced is a system trigger which has a
bug in it. The problem seems to occur when defining a
foreign key if the parent has a unique index in addition
to the primary key index. It's also sensitive to the
constraint name (!) and its ordering relative to "RDB$xxx".

If that's not your problem, could you build a small script
that creates the tables, indexes, and demonstrates the
error?

Thanks
Ann
<<<--------------------------------------

>>>--------------------------------------

Date: Fri, 24 Nov 2000 13:49:45 -0500
From: "Ann W. Harrison" <harrison@...>
Subject: RE: Cannot create a Foreign Key - Error Message - Why?
In-Reply-To: <NCBBKFPKELHKBIGCCFNAKEGHDIAA.guardian@...>
References: <5.0.1.4.2.20001122172847.01b8f438@...>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 01:18 AM 11/23/2000 -0500, Michael L. Horne wrote:

>Yes, there is 2 indexes on that field, one normal
>primary key index, and a DESC index.
>
>Do you mean that 2 indexes are what causes this
>problem?
>
>**** You are right, I dropped the DESC index on
>that field and it let me create the Foreign key.
>
>WHY????

It's a bug in an internal trigger.

>And Why doesn't it tell you what is really wrong
>in the error message?

Because it's a bug - whoever designed the trigger
assumed that no one would ever see that message.

Regards,
Ann
www.ibphoenix.com
Database Resurrectionist
<<<--------------------------------------