Subject AW: [firebird-support] Problem foreign keys
Author Olaf Kluge
Hello.

Has no one an idea?
 
________________________________________

Hello,

I've a problem with my firebid database.

Firebird 2.1 is running in classic server mode.

The follow Tables are in relationship:

Table Name: TTEILE (Item)
Teilenr varchar(8) Primary Key
Some fields

Table Name: TARBG (flow of work, step of editing)
TEILENR varchar(8)(part one of compound primary key)
BSNR integer(part two of compound primary key)
Some other fields

Foreign key:
ALTER TABLE TARBG ADD CONSTRAINT FK_TARBG FOREIGN KEY (TEILENR) REFERENCES
TTEILE(TEILENR) ON DELETE CASCADE ON UPDATE CASCADE;

Table Name: TPLAN_KW (planning for each week of the year)
Primary Key (compound):
LTJAHR integer (Year)
LTKW integer(week of year)
APLATZ integer (workstation) (for each week of the year all workstations -
planning) dependent on an other table
Some other fields

Table Name: TPLAN_KW_POS (positions of orders for each workstation in each
week of year)
ID integer primary key
LTJAHR integer
LTKW integer
AP integer
AUFTRAGSJAHR integer (year of order, dependent on other table (compound)
AUFTRAGSNR integer (number of order, dependent on other table (compound)
TEILENR varchar(8), (item)
And other fields.

Foreign Keys:
ALTER TABLE TPLAN_KW_POS ADD CONSTRAINT FK_TPLAN_KW_POS FOREIGN KEY
(LTJAHR,LTKW,AP) REFERENCES TPLAN_KW(LTJAHR,LTKW,APLATZ) ON UPDATE CASCADE;

ALTER TABLE TPLAN_KW_POS ADD CONSTRAINT FK_TPLAN_KW_POS2 FOREIGN KEY
(TEILENR) REFERENCES TTEILE(TEILENR) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE TPLAN_KW_POS ADD CONSTRAINT FK_TPLAN_KW_POS3 FOREIGN KEY
(TEILENR,ARBGANG) REFERENCES TARBG(TEILENR,BSNR) ON DELETE CASCADE ON UPDATE
CASCADE;

Now, if I change the teilenr on table tteile, I receive the follow error
message:

Violation of foreign key constraint "FK_TPLAN_KW_POS3" on table
"TPLAN_KW_POS". Foreign key reference target does not exist.

I'm wondering, cause all foreign keys are on update and theoretical the
teilenr would be changed in all tables. This works in other tables without
errors.

Should I give you the DDL of the database?

Thanks for help.

With best regards

Olaf Kluge