建表时即添加check约束
create table emp9( id number(3), name nvarchar2(20) check(name='hero' or name='ufo'), primary key(id) )
查询该表的所有约束:
select constraint_name,constraint_type,search_condition from user_constraints where table_name='EMP9'
查询结果:
CONSTRAINT_NAME -------------------------------------------------------------------------------- CO -- SEARCH_CONDITION -------------------------------------------------------------------------------- SYS_C007749 C name='hero' or name='ufo' SYS_C007750 P CONSTRAINT_NAME -------------------------------------------------------------------------------- CO -- SEARCH_CONDITION --------------------------------------------------------------------------------
删除已有约束SYS_C007749
alter table emp9 drop constraint SYS_C007749
再加上新约束:
alter table emp9 add check(name='hero' or name='ufo' or name='ceo');
最后查查是否符合预期:
select constraint_name,constraint_type,search_condition from user_constraints where table_name='EMP9' CONSTRAINT_NAME -------------------------------------------------------------------------------- CO -- SEARCH_CONDITION -------------------------------------------------------------------------------- SYS_C007751 C name='hero' or name='ufo' or name='ceo' SYS_C007750 P CONSTRAINT_NAME -------------------------------------------------------------------------------- CO -- SEARCH_CONDITION --------------------------------------------------------------------------------
确如预期,修改达成。
END