Subject | adding foreign key referencing multi-column primary key |
---|---|
Author | melanie grieb |
Post date | 2005-03-15T15:07:24Z |
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
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;
/* 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;
I am using firebird version 1.5. The error message is from Interbase
Console Version 1.0.0.326.
I am on this problem for days now, so any help is greatly appreciated.
M.Grieb
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
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;
/* 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;
I am using firebird version 1.5. The error message is from Interbase
Console Version 1.0.0.326.
I am on this problem for days now, so any help is greatly appreciated.
M.Grieb