community.borland.com

Article #25303: Adding constraint foreign key failed with SQLCODE 607

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