【Oracle】Check型约束的创建、查看和删除

有这么一张表：

create table emp5(
    id number(4),
    name nvarchar2(20),
    primary key(id)
)

如果想让它的name字段只能输入固定值如a,b,可以这样给name字段加上check：

alter table emp5 modify(name nvarchar2(20) default 'a' check(name ='a' or name ='b'));

此句完成后，测试一下：

SQL> insert into emp5(id,name) values(1,'a');

已创建 1 行。

SQL> insert into emp5(id,name) values(2,'b');

已创建 1 行。

SQL> commit;

提交完成。

从上面两句看，a,b是可以插入name字段的。

SQL> insert into emp5(id,name) values(3,'c');
insert into emp5(id,name) values(3,'c')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LUNA.SYS_C007644)


SQL> insert into emp5(id,name) values(4,'d');
insert into emp5(id,name) values(4,'d')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LUNA.SYS_C007644)

这里明显c，d就插不进去了，上面还提示了约束名称，待会我们还将看见它。

如果我们要增加c和d也能插入name字段该如何呢？用下面语句变更check条件是否可以呢？

alter table emp5 modify(name nvarchar2(20) default 'a' check(name ='a' or name ='b' or name ='c' or name ='d'));

让我们看看执行完了后能否插入？

SQL> alter table emp5 modify(name nvarchar2(20) default 'a' check(name ='a' or name ='b' or name ='c' or name ='d'));

表已更改。

SQL> insert into emp5(id,name) values(3,'c');
insert into emp5(id,name) values(3,'c')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LUNA.SYS_C007644)


SQL> insert into emp5(id,name) values(4,'d');
insert into emp5(id,name) values(4,'d')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (LUNA.SYS_C007644)

看来还是不行，这个坑很容易让人中招。我们又一次看到了恼人的SYS_C007644

这时使用 alter table emp5 drop constraint sys_c007644 这种语句删掉SYS_C007644就好了，之后旧的约束会删除，新的约束会生效。

如果你还想多学一点，请继续往下看：

 

如果我们要看一张表上有多少约束，可以使用这样的语句：

select constraint_name,constraint_type,search_condition from user_constraints where table_name='EMP5'

执行效果：

SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name='EMP5';

CONSTRAINT_NAME      CONSTRAINT_TYPE      SEARCH_CONDITION
-------------------- -------------------- --------------------
SYS_C007644          C                    name ='a' or name ='
                                          b'

SYS_C007645          C                    name ='a' or name ='
                                          b' or name ='c' or n
                                          ame ='d'

SYS_C007643          P

明显，有两个Check约束都加在name字段上，我们删掉那个范围小的就好：

SQL> alter table emp5 drop constraint sys_c007644;

表已更改。

再看看插入情况：

SQL> insert into emp5(id,name) values(3,'c');

已创建 1 行。

SQL> insert into emp5(id,name) values(4,'d');

已创建 1 行。

终于成了！最终表中数据为：

SQL> select * from emp5;

        ID NAME
---------- ----------------------------------------
         1 a
         2 b
         3 c
         4 d

END