Firebird Interview Question:

How to drop all foreign keys in database?

Submitted by: Administrator

Deleting all foreign keys can be done by querying the system tables and droping them one by one. If you use Firebird 2 or higher, it can be done with a single SQL statement:

set term !! ;

EXECUTE BLOCK RETURNS (stmt VARCHAR(1000)) AS
BEGIN
FOR
select 'alter table '||r.rdb$relation_name ||' drop constraint '||r.rdb$constraint_name||';'
from rdb$relation_constraints r
where (r.rdb$constraint_type='FOREIGN KEY')
into :stmt
DO begin suspend; execute statement :stmt; end
END!!

set term ; !!

If you use Firebird 1.x, you can run the following query to get statements to execute and then copy/paste the result and execute:

select 'ALTER TABLE '||r.rdb$relation_name
||' DROP CONSTRAINT '||r.rdb$constraint_name||';'
from rdb$relation_constraints r
where (r.rdb$constraint_type='FOREIGN KEY')
Submitted by: Administrator