At 04:07 PM 15/03/2005 +0100, you wrote:

>I am trying to reference a multi-column primary key using a foreign key...
>
>ALTER TABLE "SEQ_POSITION" ADD CONSTRAINT "FK_AL_POSITION_ID" FOREIGN
>KEY("AL_POSITION_ID")
> REFERENCES AL_POSITION("ALIGNMENT_NR", "AL_POSITION_NR")
> ON UPDATE CASCADE
> ON DELETE CASCADE
>
>...and I get the following error:
>
>Dynamic SQL Error
>SQL error code = -607
>Invalid command
>FOREIGN KEY column count does not match PRIMARY KEY
>
>Error -607 normally means that the index (FK_AL_POSITION_ID) already
>exists, but that is not the case

No, SQLCode -607 groups a series of miscellaneous metadata update errors,
the "umbrella" one being gdscode 335544351, Unsuccessful metadata
update. The material one here is 335544476, which happens when the
column(s) designated as the foreign key don't match the primary key (or
another unique key) in the referenced table.

I don't think that the statement quoted above is the one that is causing
the exception.

>The tables directly involved in the problem are:
>
>/* Table: SEQ_POSITION, Owner: LED */
>
>CREATE TABLE "SEQ_POSITION"
>(
> "SEQUENCE_NR" INTEGER NOT NULL,
> "SEQ_POSITION_NR" INTEGER NOT NULL,
> "AL_POSITION_ID" INTEGER,
>CONSTRAINT "SEQ_POSITION_PK" PRIMARY KEY ("SEQUENCE_NR", "SEQ_POSITION_NR")
>);
>ALTER TABLE "SEQ_POSITION" ADD CONSTRAINT "FK_SEQUENCE" FOREIGN KEY
>("SEQUENCE_NR") REFERENCES SEQUENCE ("SEQUENCE_NR") ON UPDATE CASCADE;

For this FK to work, the table named SEQUENCE should have a unique
constraint or a primary key constraint on "SEQUENCE_NR". Is there a table
named SEQUENCE, or is it "SEQUENCE" ?

>/* Table: AL_POSITION, Owner: LED */
>
>CREATE TABLE "AL_POSITION"
>(
> "AL_POSITION_NR" INTEGER NOT NULL,
> "SEQ_POSITION_ID" INTEGER NOT NULL,
> "ALIGNMENT_NR" INTEGER NOT NULL,
>CONSTRAINT "AL_POSITION_PK" PRIMARY KEY ("AL_POSITION_NR", "ALIGNMENT_NR")
>);
>ALTER TABLE "AL_POSITION" ADD CONSTRAINT "FK_ALIGNMENT" FOREIGN KEY
>("ALIGNMENT_NR") REFERENCES ALIGNMENT ("ALIGNMENT_NR") ON UPDATE CASCADE;

Same problem here ? Is the reference table named ALIGNMENT or "ALIGNMENT" ?
Does it have a unique constraint on the referenced column?

i.e., if those referenced tables already have these constraints, then check
whether you defined the tables with quoted identifiers, as you appear to
have done with everything else. In that case, you will need to use the
quoted identifiers to refer to them.

Also, make certain that you are committing the changes to the referenced
tables before trying to refer to them through constraints.

>I am using firebird version 1.5. The error message is from Interbase
>Console Version 1.0.0.326.

Firebird 1.5 uses its own message file. If you use a different message
file, e.g. the IB 6 or Firebird 1.0 interbase.msg available in the
application path, you will get anomalies in the messages you see.

./heLen