Problem: Executing statement " alter table table_name add constraint constraint-name foreign key (field-name) references table-name (field-name)" with error "statement failed, SQLCODE=-607". The details of the error is unsuccessful metadata update -STORE RDB$REF_CONSTRAINTS failed -action cancelled by trigger (1) to preserve data integrity -Name of Referential Constraint not defined in constraints table. Solution: IB V4.x Check and see if the referencing table's primary key is also a foreign key to another table. For example, this is the sample statement executed with the above error: alter table table2 add constraint reftable1fk foreign key (field1) references table1(field1) And this is what tables look like: ------------------------------------------ SHOW TABLE table1 FIELD1 VARCHAR(5) Not Null FIELD2 VARCHAR(5) Not Null CONSTRAINT INTEG_3: Primary key (FIELD1) CONSTRAINT REFTABLE3FK: Foreign key (FIELD1) References TABLE3 (FIELD1) SHOW TABLE table2 FIELD1 VARCHAR(5) Not Null SHOW TABLE table3 FIELD1 VARCHAR(5) Not Null CONSTRAINT INTEG_5: Primary key (FIELD1) If the setup is similiar to what's described above and you've restored the database recently, then you may be hitting a known InterBase bug. The workaround before the bug is fixed is: + drop the foreign key constraint from the referencing table + run the original statement + Add back the foreign key constraint to the referencing table Here is workaround for the above example: + alter table table1 drop constraint reftable3fk; + commit; + alter table table2 add constraint reftable1fk foreign key (field1) references table1(field1); + commit; +alter table table1 add constraint reftable3fk foreign key (field1) references table3(field1); + commit;
Last Modified: 12-OCT-00