At 02:29 PM 13/10/2003 +0200, you wrote:

>Hi all,
>
>I need to implement strict referential integrity for an accounts db. I keep
>on getting the following error :
>
>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
>Statement: ALTER TABLE "ACCOUNTS" ADD CONSTRAINT "FK_ACCOUNT_ACCOUNT_TYPE"
>FOREIGN KEY ("ACCOUNT_TYPE") REFERENCES ACCOUNTTYPETABLE ("RECORDNO")
>
>Metadata for Sub accounts:
>
>CREATE TABLE "SUB_ACCOUNTS"
>(
> "RECORDNO" "DOM_RECNUM",
> "ACCOUNT_NUMBER" "DOM_CODE",
> "SUB_ACCOUNT_NUMBER" "DOM_CODE",
> "SUB_ACCOUNT_CODE" "DOM_CODESTRING",
> "SUB_ACCOUNT_NAME" "DOM_DESCRIP",
> "SUB_ACCOUNT_TYPE" "DOM_ACCTYPE",
> "SUB_TAX_TYPE" "DOM_TAXRATING",
> PRIMARY KEY ("RECORDNO"),
>CONSTRAINT "UN_SUBACC_ACCOUNTCODE" UNIQUE ("SUB_ACCOUNT_CODE"),
>CONSTRAINT "UN_SUBACC_ACCOUNTNUMBER" UNIQUE ("SUB_ACCOUNT_NUMBER")
>);
>
>Metadata for Account Type Table :
>
>/* Table: ACCOUNTTYPETABLE, Owner: SYSDBA */
>
>CREATE TABLE "ACCOUNTTYPETABLE"
>(
> "RECORDNO" "DOM_RECNUM",
> "DESCRIPTION" "DOM_DESCRIP",
> "ABBREVIATION" "DOM_ABBREV",
> PRIMARY KEY ("RECORDNO"),
>CONSTRAINT "UN_ACTYPE_ABBREV" UNIQUE ("ABBREVIATION")
>);
>
>Anyone know what I'm doing wrong?

Did you paste the wrong metadata here?

Maybe this is just God's way of telling you that this would be a **very
bad** foreign key. (it would, you know!) Write your own trigger to
enforce the integrity, and avoid that terrible index you're going to get if
you implement that FK.

heLen