如何更改 Firebird 上的 CHECK 约束? [英] How do I alter a CHECK constraint on Firebird?

问题描述

我有一个像这样的 Firebird 桌子:

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
);

现在我需要向 CHECK 约束中的 IN() 列表添加另一个值.我该怎么做?

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:

• 更新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''))';

似乎不起作用，因为触发器是在 RDB$TRIGGERS.RDB$TRIGGER_BLR 中编译的.

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

使用新检查创建新表，复制数据，删除旧表并重命名新表.但是，似乎无法重命名 Firebird 表，因此我无法使新表与旧的.

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.

我怀疑更新 RDB$TRIGGERS 是要走的路(idk！)，如果我能让 Firebird 重新编译代码.但也许有更好的方法?

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?

推荐答案

您需要删除并重新创建检查约束.

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

由于您没有为约束指定名称，Firebird 创建了一个名称，因此您首先需要找到该名称:

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;

然后添加新的约束:

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.

这篇关于如何更改 Firebird 上的 CHECK 约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！