Firebird: Alter "anonymous" foreign key

Question

I need to alter an existing foreign key from "on delete restrict" to "on delete cascade". Unfortunaltey this bug sneaked through Q/A.

In my database I have several forign key relationships that were automatically named (INTEG_1, INTEG_2, ...). The name of the constraint I have to fix is another in a new installation than in an Update from Version 2 and even another than when this Version 2 previously has been updated from Version 1.

As the referencing table only has one foreign key, this statement gives me the name of the constraint:

SELECT RDB$CONSTRAINT_NAME
FROM RDB$RELATION_CONSTRAINTS
where RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' 
  and RDB$RELATION_NAME = 'MY_TABLE_NAME'

then I can drop and afterwards recreate the foreign key (with a "real" name this time)

alter table MY_TABLE_NAME 
drop constraint <result from above>;

alter table MY_TABLE_NAME
add constraint fk_my_table_name_purpose foreign key (other_id) 
      references other_table(id) on delete cascade;

However, I try to avoid working directly with system tables and I'd like to know whether there is a better / more elegant way to alter my foreign key.

Answer 1

There's no better way, the system tables are the only way to figure out the constraint name.