【Oracle】给emp9表的name字段'设定'、'删除'和'新增'检查性约束的全过程语句

建表时即添加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