How do I check if a constraint exists on Firebird?

Question

I'm about to publish a script which will update a lot of Firebird databases all at once. Some will not have this constraint, so I would like to check for the existence of a constraint before I try to drop it.

ALTER TABLE PROCESS_CATEGORY DROP CONSTRAINT INTEG_669;

Answer 1

You can query RDB$RELATION_CONSTRAINTS table, if you know the name of the constraint. Something like this:

set term ^;
execute block as
begin
    if (exists(
        select 0 from rdb$relation_constraints
        where rdb$constraint_name = 'INTEG_669'
    )) then
        execute statement 'alter table process_category drop constraint INTEG_669';
end
^
set term ;^

Used execute statement because alter table not allowed inside the block and if statement not allowed outside.