【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