How do I alter a CHECK constraint on Firebird?

Question

I have a Firebird table like this:

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 IN() list in the CHECK constraint. How do I do that?

Things I've tried so far:

Updating the value in 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''))';

Does not seem to work, as the trigger is compiled in RDB$TRIGGERS.RDB$TRIGGER_BLR.

Creating a new table with a new check, copying the data over, dropping the old table and renaming the new table. However, it seems that one cannot rename a Firebird table, so I can't make the new table have the same name as the old one.

I suspect updating RDB$TRIGGERS is the way to go (idk!), if only I could get Firebird to recompile the code. But maybe there's a better way?

score 4 · Accepted Answer · 2015-01-07T23:32:43.910

You need to drop and the re-create the check constraint.

As you didn't specify a name for your constraint, Firebird created one, so you first need to find that name:

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;

I just added the trigger source for informational reasons.

Once you have the name, you can drop it, e.g.

alter table events drop constraint integ_27;

and then add the new constraint:

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

In the future you don't need to look for the constraint name because you already it.

Is there a way to programmatically get the constraint name and do this work in a file passed to `isql`? — theory, Jan 08 '15 at 00:12
I figured out how do do it programmatically and posted it in a separate answer. — theory, Jan 08 '15 at 01:57

theory · Answer 2 · 2015-01-09T02:05:09.413

Here's how to do it dynamically:

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;

I had to disable AUTOddl and put in explicit commits or else I got a deadlock on the ALTER TABLE ADD CONSTRAINT statement.

A word of caution: although this works, executing DDL from PSQL (like EXECUTE BLOCK) is not allowed precisely because of these types of errors (+ additional reasons having to do with how PSQL is compiled and - in the case of stored procedures - is stored), and although using `EXECUTE STATEMENT` allows you to circumvent this you should really only do that if you have no other way to solve this (which in this specific case you probably don't have). — Mark Rotteveel, Jan 08 '15 at 06:59

Martin Miguel Parody Narvaez · Answer 3 · 2021-09-08T00:39:33.100

Here's how to do it dynamically:

EXECUTE BLOCK RETURNS (STMT VARCHAR(1000)) AS
BEGIN
    SELECT TRIM(R.RDB$CONSTRAINT_NAME)
    FROM RDB$RELATION_CONSTRAINTS R
    WHERE R.RDB$RELATION_NAME = 'TABLE_NAME'
    AND UPPER(R.RDB$CONSTRAINT_TYPE) = UPPER('PRIMARY KEY')
    INTO :STMT;

    IF (:STMT IS NOT NULL) THEN
    BEGIN
        EXECUTE STATEMENT 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' || :STMT || ';';
        EXECUTE STATEMENT 'ALTER TABLE TABLE_NAME ADD CONSTRAINT ' || :STMT || ' PRIMARY KEY (FIELD1, FIELD2, FIELD3);';
    END
    ELSE
    BEGIN
        EXECUTE STATEMENT 'ALTER TABLE FIELD1 ADD CONSTRAINT PK_PRIMARY_NAME PRIMARY KEY (FIELD1, FIELD2, FIELD3);';
    END
END;

How do I alter a CHECK constraint on Firebird?

3 Answers3

Linked

Related