How to change CHECK restriction on Firebird?

Question

I have a Firebird table as follows:

CREATE TABLE events (
    event      VARCHAR(6)    NOT NULL
               CHECK (event IN ('deploy', 'revert', 'fail')),
    change_id  CHAR(40)      NOT NULL,
    change     VARCHAR(512)  NOT NULL
);

Now I need to add another value to the list IN()in the CHECK constraint. How to do it?

Things I've tried so far:

• Update value to RDB$TRIGGERS.RDB$TRIGGER_SOURCE:

  UPDATE RDB$TRIGGERS
     SET RDB$TRIGGER_SOURCE = 'CHECK (event IN (''deploy'', ''revert'', ''fail'', ''merge''))'
   WHERE RDB$TRIGGER_SOURCE = 'CHECK (event IN (''deploy'', ''revert'', ''fail''))';
  

  It doesn't seem to work as the trigger is compiled into RDB$TRIGGERS.RDB$TRIGGER_BLR.

• Create a new table with a new check, copy data, delete the old table and rename the new table. However, it seems that it cannot rename the Firebird table , so I cannot force the new table to have the same name as the old one.

I suspect that updating RDB$TRIGGERSis the way to go (idk!), If only I can get Firebird to recompile the code. But maybe there is a better way?

Answer 1

.

, Firebird , :

select trim(cc.rdb$constraint_name), trg.rdb$trigger_source 
from rdb$relation_constraints rc
  join rdb$check_constraints cc on rc.rdb$constraint_name = cc.rdb$constraint_name 
  join rdb$triggers trg on cc.rdb$trigger_name = trg.rdb$trigger_name 
where rc.rdb$relation_name = 'EVENTS'
and   rc.rdb$constraint_type = 'CHECK' 
and   trg.rdb$trigger_type = 1;

.

, , .

alter table events drop constraint integ_27;

:

alter table events 
    add constraint check_event_type 
        CHECK (event IN ('deploy', 'revert', 'fail', 'merge'));

, .

Answer 2

, :

SET AUTOddl OFF;
SET TERM ^;
EXECUTE BLOCK AS
    DECLARE trig VARCHAR(64);
BEGIN
    SELECT TRIM(cc.rdb$constraint_name)
      FROM rdb$relation_constraints rc
      JOIN rdb$check_constraints cc ON rc.rdb$constraint_name = cc.rdb$constraint_name
      JOIN rdb$triggers trg         ON cc.rdb$trigger_name    = trg.rdb$trigger_name
     WHERE rc.rdb$relation_name   = 'EVENTS'
       AND rc.rdb$constraint_type = 'CHECK'
       AND trg.rdb$trigger_type   = 1
      INTO trig;
    EXECUTE STATEMENT 'ALTER TABLE EVENTS DROP CONSTRAINT ' || trig;
END^

SET TERM ;^
COMMIT;

ALTER TABLE events ADD CONSTRAINT check_event_type CHECK (
    event IN ('deploy', 'revert', 'fail', 'merge')
);
COMMIT;

AUTOddl , ALTER TABLE ADD CONSTRAINT.