Hi,

> =============================== 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");

"Acc ID" allrady first segment of PK,
so what is the trick of another index on "Acc ID" ?

PK doesnt seen effected to me, only ID should be enough in my mind.

>
> ============================= 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);
>

i think you need to review your PK build strategy.

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

As you can see easly,
FB says there is no Unique index on "Acc ID" allready required.
your defined index on "Acc ID" is duplicated index, not unique:

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

Regards.
Ali