FireBird adding dropping constraint

Question

In Firebird I want to generate the scripts for adding all the constraints in a database and also I want to generate the script for dropping all the foreign key constraints.

How do I do this?

Answer 1

To generate a script which in turns drop all the foreign keys, use the following script in any firebird >= 2.0 (maybe it works on pre 2.0, just can't remember if data dictionary changed in involved system table):

--generate a script which drops all foreign keys
--by jachguate http://jachguate.wordpress.com
--             http://stackoverflow.com/users/255257/jachguate
select 'alter table '||c.rdb$relation_name||' drop constraint '||c.rdb$constraint_name||';' script_lines
  from rdb$relation_constraints c
 where c.rdb$constraint_type = 'FOREIGN KEY';

Edit

To generate a script to re-create all the foreign keys, this script will do the trick (for firebird >= 2.0). Remember to run this before actually deleting the foreign keys.

select 'alter table '||trim(c.rdb$relation_name)
       ||' add constraint '||trim(c.rdb$constraint_name)
       ||' foreign key ('
       ||(select list(trim(imast.rdb$field_name)) from rdb$index_segments imast where rdb$index_name = c.rdb$index_name)
       ||') references '||trim(uqc.rdb$relation_name)
       ||' ('
       ||(select list(trim(idet.rdb$field_name)) from rdb$index_segments idet where rdb$index_name = uqc.rdb$index_name)
       ||');'
  from rdb$relation_constraints c
       inner join rdb$ref_constraints rc
          on rc.rdb$constraint_name = c.rdb$constraint_name
       inner join rdb$relation_constraints uqc
          on uqc.rdb$constraint_name = rc.rdb$const_name_uq;

Best regards.