oracle银行卡号检查约束,oracle约束学习（1）unique和check

有人说，没有索引, 拿什么来保证约束?姑且不论这话的对错，但约束的实现(除了not null)，很多都是通过索引来快速定位约束的地方。unique约束会自动建立索引，pk也是。也因此，约束的很多问题总是和索引缠绵一起。

相关视图：

dba_constraints

dba_cons_columns

not null约束比较特别点。not null的索引类型和check约束一样，都是C。只有not null这种约束才会被继承，记住这一点，在复制表时，别忘了加约束、索引等。

unique  约束

延迟验证属性：deferrable/not deferrable, Deferred/immediate

延迟约束在提交的时候才进行校验，大多数是这样的。

not deferrable：不提交也检查，这是缺省值。

SQL> select * from hr.t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

SQL> conn hr/hr

已连接。

SQL> alter table t1 add constraint t1_u_1 unique(id); -- 默认情况下是not deferrable & immediate

表已更改。

SQL> insert into t1 values(1,\'c\');

insert into t1 values(1,\'c\')

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (HR.T1_U_1)

SQL> alter table t1 drop constraint t1_u_1 cascade;

表已更改。

SQL> alter table t1 add constraint t1_u_2 unique(id) deferrable; --deferrable & immediate

表已更改。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

SQL> insert into t1 values(1,\'c\');

insert into t1 values(1,\'c\')

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (HR.T1_U_2)

为什么延迟还还报错呢？延迟性约束，索引必须非唯一。enable novalidate也必须是非唯一索引。然而，我们在建unique约束的时候，oracle会隐士的先创建一个唯一性索引，而且索引的名字和约束的名字是一样的。再来建立约束，除非这个列本来有一个约束。

SQL> alter table t1 add constraint t1_u_6 unique(id);

表已更改。

SQL> select index_name,uniqueness from user_indexes ;

INDEX_NAME UNIQUENESS

------------------------------------------------------------ ------------------

T1_U_6 UNIQUE

SQL> alter table t1 add constraint t1_u_3 unique(id) deferrable; --deferrable & immediate

表已更改。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

SQL> insert into t1 values(1,\'c\');

insert into t1 values(1,\'c\')

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (HR.T1_U_3)

SQL> alter table t1 drop constraint t1_u_3 cascade;

表已更改。

SQL> drop index t1_index;

索引已删除。

SQL> create unique index t1_index on t1(id);

索引已创建。

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENESS

------------------------------------------------------------ ------------------

T1_INDEX UNIQUE

SQL> alter table t1 add constraint t1_u_4 unique(id) deferrable;

alter table t1 add constraint t1_u_4 unique(id) deferrable

*

第 1 行出现错误:

ORA-01408: 此列列表已索引

SQL> drop index t1_index;

索引已删除。

SQL> alter table t1 drop constraint t1_u_4 cascade;

alter table t1 drop constraint t1_u_4 cascade

*

第 1 行出现错误:

ORA-02443: 无法删除约束条件 - 不存在的约束条件

SQL> alter table t1 add constraint t1_u_5 unique(id) initially deferred deferrable; --若是not deferrable，则只能跟immediate

表已更改。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

SQL> insert into t1 values(1,\'c\');

已创建 1 行。

SQL> insert into t1 values(1,\'d\');

已创建 1 行。

SQL> commit;

commit

*

第 1 行出现错误:

ORA-02091: 事务处理已回退

ORA-00001: 违反唯一约束条件 (HR.T1_U_5)

immediate deferrable是立马延迟，而deferred deferrable则是在commit时给回滚掉。

check

validate会检查旧值，而novalidate不会。这个运用可以用来避免前台小姐误操作。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

3 c

4 c

SQL> alter table t1 add constraint t1_u_6 check(id>10) validate;

alter table t1 add constraint t1_u_6 check(id>10) validate

*

第 1 行出现错误:

ORA-02293: 无法验证 (HR.T1_U_6) - 违反检查约束条件

SQL> alter table t1 add constraint t1_u_6 check(id>10) novalidate;

表已更改。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

3 c

4 c

SQL> insert into t1 values(5,\'d\');

insert into t1 values(5,\'d\')

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> alter table t1 add constraint t1_u_6 check(id>10) enable novalidate;

表已更改。

SQL> insert into t1 values(6,\'d\');

insert into t1 values(6,\'d\')

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

3 c

4 c

SQL> alter table t1 add constraint t1_u_6 check(id>10) disable novalidate;

表已更改。

SQL> select * from t1;

ID NAME

---------- ----------------------------------------

1 d

2 b

3 c

4 c

SQL> insert into t1 values(5,\'s\');

已创建 1 行。

SQL> commit;

提交完成。

约束的优先级比约束的属性来得高。

SQL> create table t1 (id number,constraint t1_u_6 check(id>3) validate);

表已创建。

SQL> insert into t1 values(1);

insert into t1 values(1)

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> insert into t1 values(5);

已创建 1 行。

SQL> drop table t1 purge;

表已删除。

SQL> create table t1(id number,constraint t1_u_6 check(id>3) novalidate);

表已创建。

SQL> insert into t1 values(1);

insert into t1 values(1)

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> insert into t1 values(5);

已创建 1 行。

SQL> select * from t1;

ID

----------

5

SQL> alter table t1 add constraint t1_u_6 check(id>10) novalidate;

表已更改。

SQL> insert into t1 values(3);

insert into t1 values(3)

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> insert into t1 values(7);

insert into t1 values(7)

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (HR.T1_U_6)

SQL> insert into t1 values(11);

已创建 1 行。

SQL> select * from t1;

ID

----------

5

11