D'OH! Not confused any more.

A Unique Index is not the same as a Unique Constraint, which is what
Cust(ID) needs to be.

Thanks, Ali.




-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of
firebird@...
Sent: Sunday, July 09, 2006 1:15 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] FK confusion



Given the following (truncated) DDL statements:

=============================== A Table =====================
CREATE TABLE "ACCCHEM" (
"Acc ID" INTEGER NOT NULL,
"Date" DATE NOT NULL,
"ID" INTEGER NOT NULL,
"Chem ID" INTEGER,
"Chemical Brand" VARCHAR(40),
"Complete Name" VARCHAR(40),
"EPA Registration #" VARCHAR(30),
----- removed many fields -----
CONSTRAINT "PK_ACCCHEM" PRIMARY KEY ("Acc ID", "Date", "ID")
);

CREATE INDEX "IDX_ACCCHEM_1" ON "ACCCHEM" ("Acc ID");

CREATE INDEX "IDX_ACCCHEM_2" ON "ACCCHEM" ("Chemical Brand","Chem ID");

============================= Another Table =====================
CREATE TABLE "Cust" (
"Cat" SMALLINT NOT NULL,
"Last Name" VARCHAR(35) NOT NULL,
"ID" INTEGER NOT NULL,
"First Name" VARCHAR(15),
"Street" VARCHAR(30),
"Town" VARCHAR(15),
"State" VARCHAR(2),
"Zip" VARCHAR(10),
----- removed many fields -----
CONSTRAINT "PK_Cust" PRIMARY KEY ("Cat", "Last Name", "ID")
);

----- removed a bunch of other index defs -------

CREATE UNIQUE INDEX "IDX_Cust_13" ON "Cust" (ID);


===================== Alter Table doesn't work
==================================
ALTER TABLE "ACCCHEM" ADD CONSTRAINT "Cust_ACCCHEM"
FOREIGN KEY ("Acc ID") REFERENCES "Cust" (ID) ON DELETE CASCADE ON
UPDATE CASCADE;

I get this error message:

This operation is not defined for system tables.Unsuccessful metadata
update.
Could not find UNIQUE INDEX with specified columns.

IDX_Cust_13 is there, though. What am I overlooking?


Michael D. Spence
Mockingbird Data Systems, Inc.


[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]