Firebird SQL: Subquery within an ALTER statement?

I have a firebird database schema which was set up by various CREATE TABLE scripts. These scripts have been commited to a source control system (see Scott Allen's sites).

Now I want to create a new script in which I want my database to drop the NOT NULL constraint 开发者_如何学JAVAfor one column in a specific table:

ALTER TABLE myTable DROP CONSTRAINT c

The constraint's name (e.g. INTEG_219) can be obtained by a query to a system table:

SELECT rc.rdb$constraint_name
FROM   rdb$relation_constraints rc
       join rdb$check_constraints cc
       on rc.rdb$constraint_name = cc.rdb$constraint_name
WHERE  rc.rdb$constraint_type   = 'NOT NULL'
       and rc.rdb$relation_name = 'MYTABLE'
       and cc.rdb$trigger_name  = 'ACOLUMN'

Can these statements be combined to a single ALTER command?

I advice you to take a look at EXECUTE STATEMENT.

and here in the firebird faq

execute BLOCK 
as declare variable stmt VARCHAR(2500) = 'SELECT ''ALTER TABLE MYTABLE myTable DROP CONSTRAINT ''|| rc.rdb$constraint_name||'';'' FROM   rdb$relation_constraints rc
           join rdb$check_constraints cc
           on rc.rdb$constraint_name = cc.rdb$constraint_name WHERE  rc.rdb$constraint_type   = ''NOT NULL''
           and rc.rdb$relation_name = ''MYTABLE''
           and cc.rdb$trigger_name  = ''ACOLUMN'''; 
begin   
  execute STATEMENT stmt; 
end;

This answer is based on Hughes Van Landeghem's answer and Norbert's response to that answer, so feel free not to vote it up :)

execute BLOCK 
as 
    declare variable stmt VARCHAR(2500); 
    declare variable cname varchar(255);
begin
    SELECT rc.rdb$constraint_name
        FROM   rdb$relation_constraints rc
               join rdb$check_constraints cc
               on rc.rdb$constraint_name = cc.rdb$constraint_name
        WHERE  rc.rdb$constraint_type   = 'NOT NULL'
               and rc.rdb$relation_name = 'MYTABLE'
               and cc.rdb$trigger_name  = 'ACOLUMN'
        INTO :cname;
    stmt = 'alter table myTable drop constraint ' || cname;
    execute STATEMENT stmt; 
end;

I'm also using Firebird 2.5 and could not get Hughes Van Landeghem's original answer to run. The error I got was No message for error code 335544928 found, and I think it has something to do with the embedded select.