Oracle：如何用案例制定复杂的约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10716077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: how to formulate a complex constraint with case when
提问by Revious
The following conditional constraint simply doesn't work. Is it possible to formulate in a working way?
以下条件约束根本不起作用。是否可以以工作方式制定？
ALTER TABLE eni_trasc_voci_pwr_fatt
ADD CONSTRAINT tvp_constraint_1 CHECK (
CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione IS NULL
END);
回答by int2000
Try the following:
请尝试以下操作：
ALTER TABLE eni_trasc_voci_pwr_fatt
ADD CONSTRAINT tvp_constraint_1 CHECK (
CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione else null end IS NULL);
回答by Judge Mental
It looks like you want logical implication here ("if X then Y"), which is logically equivalent to "(not X) or Y". CASE is used to create a finite map.
看起来你想要这里的逻辑含义（“if X then Y”），这在逻辑上等同于“（not X）或Y”。CASE 用于创建有限映射。
Your constraint should be something like
你的约束应该是这样的
TVP_CODICE_ASSOGGETAMEN != '-' OR TVP_REGIONE IS NULL
TVP_CODICE_ASSOGGETAMEN != '-' OR TVP_REGIONE IS NULL
回答by Stephen ODonnell
I think you can do what you want without the case statement:
我认为你可以在没有 case 语句的情况下做你想做的事：
create table t1 (c1 varchar2(10), c2 varchar2(10));
alter table t1 add constraint t1_chk1 check ( (c1 = '-' and c2 is null) or (c1 != '-' and c2 is not null) );
Now try and insert some values:
现在尝试插入一些值：
SQL> insert into t1 values ('-', 'reject');
insert into t1 values ('-', 'reject')
*
ERROR at line 1:
ORA-02290: check constraint (SODONNEL.T1_CHK1) violated
SQL>
SQL> insert into t1 values ('-', null);
1 row created.
SQL>
SQL> insert into t1 values ('a', null);
insert into t1 values ('a', null)
*
ERROR at line 1:
ORA-02290: check constraint (SODONNEL.T1_CHK1) violated
SQL>
SQL> insert into t1 values ('a', 'accept');
1 row created.
