我的Oracle 9i学习日志（18）-- 维护数据完整性.b

创建禁止性约束：

•加disable关键字即可。

SQL> create table t(id int, name char(10));

表已创建。

SQL> desc t

名称是否为空? 类型

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

ID NUMBER(38)

NAME CHAR(10)

SQL> alter table t add constraint uq_t_id unique(id) disable;

表已更改。

SQL> insert into t values(0,'aaa');

已创建 1 行。

SQL> insert into t values(0,'aaa');

已创建 1 行。

SQL>

SQL> insert into t values(0,'aaa');

已创建 1 行。

SQL> insert into t values(0,'aaa');

已创建 1 行。

SQL> select * from t;

ID NAME

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

0 aaa

启用约束：

SQL> delete from t;

已删除4行。

SQL> alter table t enable unique;

alter table t enable unique

ERROR 位于第 1 行:

ORA-00906: missing left parenthesis

SQL> alter table t enable unique(id);

表已更改。

SQL> insert into t values(0,'aaa');

已创建 1 行。

SQL> insert into t values(0,'aaa');

insert into t values(0,'aaa')

ERROR 位于第 1 行:

ORA-00001: unique constraint (LUO.UQ_T_ID) violated

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

DINGDAN TABLE

ORDERS TABLE

PK_CUST INDEX

T TABLE

UQ_EMP_ID INDEX

UQ_T_ID INDEX

已选择8行。

SQL> drop table t;

表已丢弃。

SQL> create table t(id int, name char(10));

表已创建。

SQL> alter table t add constraint pk_t_id primary key(id) disable;

表已更改。

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

DINGDAN TABLE

ORDERS TABLE

PK_CUST INDEX

T TABLE

UQ_EMP_ID INDEX

已选择7行。

SQL> insert into t values(1,'aaa');

已创建 1 行。

SQL> insert into t values(1,'bbb');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t;

ID NAME

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

1 aaa

1 bbb

SQL> delete from t where name='bbb';

已删除 1 行。

SQL> commit ;

提交完成。

SQL> alter table t enable primary key; #启用主键约束可以不用带列名。

表已更改。

SQL> insert into t values(1,'bbb');

insert into t values(1,'bbb')

ERROR 位于第 1 行:

ORA-00001: unique constraint (LUO.PK_T_ID) violated

SQL> insert into t values(2,'bbb');

已创建 1 行。

SQL> commit ;

提交完成。

SQL> select * from t;

ID NAME

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

1 aaa

2 bbb

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

DINGDAN TABLE

ORDERS TABLE

PK_CUST INDEX

PK_T_ID INDEX

T TABLE

UQ_EMP_ID INDEX

已选择8行。

SQL> alter table t disable primary key; #再次禁用。

表已更改。

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

DINGDAN TABLE

ORDERS TABLE

PK_CUST INDEX

T TABLE

UQ_EMP_ID INDEX

已选择7行。 #索引消失。

删除约束：

Alter table bonus drop constraint ck_bonus;

删除unique或主键约束时，如果是被引用为外键的则带上cascade参数，删除unique约束同时删除了子表的外键约束。

Alter table bonus drop primary key cascade;

实验：

SQL> select constraint_name,status,validated from user_constraints;

CONSTRAINT_NAME STATUS VALIDATED

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

SYS_C002763 ENABLED VALIDATED

CK_BONUS ENABLED VALIDATED

CK_BONUS2 ENABLED VALIDATED

UQ_EMP_ID ENABLED VALIDATED

PK_CUST ENABLED VALIDATED

FK_CUST ENABLED VALIDATED

CK_DPTID ENABLED VALIDATED

已选择7行。

SQL> alter table bonus drop constraint ck_bonus2;

表已更改。

SQL> select constraint_name,status,validated from user_constraints;

CONSTRAINT_NAME STATUS VALIDATED

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

SYS_C002763 ENABLED VALIDATED

CK_BONUS ENABLED VALIDATED

UQ_EMP_ID ENABLED VALIDATED

PK_CUST ENABLED VALIDATED

FK_CUST ENABLED VALIDATED

CK_DPTID ENABLED VALIDATED

已选择6行。

SQL> alter table customers drop primary key cascade;

表已更改。

SQL> select constraint_name,status,validated from user_constraints;

CONSTRAINT_NAME STATUS VALIDATED

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

SYS_C002763 ENABLED VALIDATED

CK_BONUS ENABLED VALIDATED

UQ_EMP_ID ENABLED VALIDATED

CK_DPTID ENABLED VALIDATED

约束状态：

图5

可以启用(ENABLE) 或禁用(DISABLE) 完整性约束。如果启用某个约束，则在数据库中输入或更新数据时，就会对数据进行检查。禁止输入不符合约束规则的数据。如果禁用某个约束，则可以在数据库中输入不符合约束规则的数据。完整性约束可处于以下状态之一：

• DISABLE NOVALIDATE

• DISABLE VALIDATE

• ENABLE NOVALIDATE

• ENABLE VALIDATE

DISABLE NOVALIDATE：不检查处于DISABLE NOVALIDATE 状态的约束。表中的数据（包括输入或更新的新数据）可以不符合约束所定义的规则。

DISABLE VALIDATE：当约束处于此状态时，不允许对受约束的列进行任何修改。另外，约束上的索引将被删除并且禁用约束。注：如果约束可延迟，则不删除索引。

ENABLE NOVALIDATE：如果约束处于此状态，则不能输入违反约束的新数据。但是，表可能包含无效的数据，即数据违反约束。启用处于NOVALIDATE 状态的约束对正在上载有效OLTP 数据的数据仓库配置是非常有用的。

ENABLE VALIDATE：如果约束处于此状态，则不能将违反约束的行插入到表中。但是，禁用该约束时，可以插入此类行。此类行称为该约束的例外。如果约束处于ENABLE NOVALIDATE 状态，则在禁用约束时输入的数据所引起的违反情况仍然存在。要将约束置于已验证状态，必须更新或删除违反约束的行。

当某一约束由禁用状态更改为ENABLE VALIDATE 时，将锁定表并对表中的所有数据进行一致性检查。这可能会引起DML 操作（如等待数据加载），因此，建议先从禁用状态转为ENABLE NOVALIDATE，然后再转为ENABLE VALIDATE。

这些状态之间的转换须符合以下规则：

• 除非指定NOVALIDATE，否则ENABLE 表示VALIDATE。

• 除非指定VALIDATE，否则DISABLE 表示NOVALIDATE。

• VALIDATE 和NOVALIDATE 没有缺省的ENABLE 和DISABLE 状态。

• 当唯一键或主键从DISABLE 状态转为ENABLE 状态且没有现有索引时，将自动创建唯一索引。（如果索引可延迟，则将存在异常。）与此类似，当唯一键或主键从ENABLE 转为DISABLE 且是使用唯一索引启用时，则删除该唯一索引。

• 当任何约束从NOVALIDATE 状态转为VALIDATE 状态时，必须检查所有的数据。但是，从VALIDATE 转为NOVALIDATE 时，将忽略数据已经过检查这一事实。

• 将单个约束从ENABLE NOVALIDATE 状态转为ENABLE VALIDATE 状态时，并不禁止使用读取、写入或其它DDL 语句。

SQL> select constraint_name,table_name,status,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

CK_BONUS BONUS ENABLED VALIDATED

UQ_EMP_ID BONUS ENABLED VALIDATED

SQL> desc t

名称是否为空? 类型

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

ID NUMBER(38)

NAME CHAR(10)

DPT_ID NUMBER(38)

SQL> alter table t add constraint ck_dptid check(dpt_id>=10) disable;

表已更改。

SQL> insert into t values(1,'aaa ',1);

已创建 1 行。

SQL> insert into t values(2,'bbb',2);

已创建 1 行。

SQL> insert into t values(3,'ccc',3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t;

ID NAME DPT_ID

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

1 aaa 1

2 bbb 2

3 ccc 3

SQL> alter table t enable novalidate constraint ck_dptid;

表已更改。

SQL> select constraint_name,table_name,status,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

CK_BONUS BONUS ENABLED VALIDATED

UQ_EMP_ID BONUS ENABLED VALIDATED

CK_DPTID T ENABLED NOT VALIDATED

SQL> insert into t values(3,'ddd',4);

insert into t values(3,'ddd',4)

ERROR 位于第 1 行:

ORA-02290: check constraint (LUO.CK_DPTID) violated

SQL> insert into t values(3,'ddd',11);

已创建 1 行。

#启用enable novalidate状态成功。

SQL> alter table t add constraint pk_t_id primary key(id) disable;

表已更改。

SQL> select constraint_name,table_name,status,validated from user_constrai

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

CK_BONUS BONUS ENABLED VALIDATED

UQ_EMP_ID BONUS ENABLED VALIDATED

CK_DPTID T ENABLED NOT VALIDATED

PK_T_ID T DISABLED NOT VALIDATED

SQL> select * from t;

ID NAME DPT_ID

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

1 aaa 1

2 bbb 2

3 ccc 3

3 ddd 11

SQL> alter table t enable novalidate primary key;

alter table t enable novalidate primary key

ERROR 位于第 1 行:

ORA-02437: cannot validate (LUO.PK_T_ID) - primary key violated

#报错的原因是因为启用主键约束后会创建一个非唯一性索引，但表中有重复值，索引创建不成功。解决方法可以删除表中主键列的重复值，或用下面的方法。

SQL> create index t_id on t(id);

索引已创建。

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

ORDERS TABLE

T TABLE

T_ID INDEX

UQ_EMP_ID INDEX

已选择6行。

SQL> alter table t enable novalidate primary key;

表已更改。

SQL> insert into t values(2,'eee',12);

insert into t values(2,'eee',12)

ERROR 位于第 1 行:

ORA-00001: unique constraint (LUO.PK_T_ID) violated

SQL> insert into t values(4,'eee',12);

已创建 1 行。

当有大量数据处理时，一致性约束同索引一样会降低数据库性能。因此，可以按以下方法提高性能。

图6

约束检查时间：

图7

在约束定义完成以后，nondeferred与deferred不能相互转变。

SET CONSTRAINTS 语句用于将特定事务处理的约束设置为DEFERRED 或IMMEDIATE。

可以使用此语句设置约束名称列表或约束的模式。SET CONSTRAINTS 模式将一直持续到事务处理完成或者另一个SET CONSTRAINTS 语句重置模式。SET CONSTRAINTS 语句不允许在触发器内部使用。

ALTER SESSION 语句还包含将约束设置为IMMEDIATE 或DEFERRED 的子句SET CONSTRAINTS。此命令缺省为设置所有(ALL) 可延迟的约束（不能指定约束名称列表）。

ALTER SESSION SET CONSTRAINTS 语句仅适用于当前的会话。

ALTER SESSION

SET CONSTRAINT[S] =

{IMMEDIATE|DEFERRED|DEFAULT}

SET CONSTRAINT | CONSTRAINTS

{constraint |ALL }

{IMMEDIATE|DEFERRED}

实验：

SQL> create table t(id int,name char(10),dpt_id int);

表已创建。

SQL> desc t;

名称是否为空? 类型

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

ID NUMBER(38)

NAME CHAR(10)

DPT_ID NUMBER(38)

SQL> alter table t add constraint pk_t_id primary key(id);

表已更改。

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENESS

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

PK_T_ID UNIQUE

SQL> alter table t drop primary key;

表已更改。

SQL> alter table t add constraint pk_t_id primary key(id) deferrable;

表已更改。

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENESS

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

PK_T_ID NONUNIQUE

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

PK_T_ID INDEX

T TABLE

已选择6行。

SQL> alter table t disable primary key;

表已更改。

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

PK_T_ID INDEX

T TABLE

已选择6行。

#如果索引是非唯一性索引，在disable主键约束时，不会删除相应索引。

SQL> insert into t values(1,'aaa',1);

已创建 1 行。

SQL> insert into t values(1,'aaa',1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select constraint_name,table_name,status,validated from user_constrai

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

PK_T_ID T DISABLED NOT VALIDATED

SQL> alter table t enable novalidate primary key;

表已更改。

SQL> select constraint_name,table_name,status,validated from user_constrai

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

PK_T_ID T ENABLED NOT VALIDATED

SQL> insert into t values(1,'aaa',1);

insert into t values(1,'aaa',1)

ERROR 位于第 1 行:

ORA-00001: unique constraint (LUO.PK_T_ID) violated

SQL> select * from t;

ID NAME DPT_ID

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

1 aaa 1

SQL> set constraints all deferred;

约束条件已设置。

SQL> insert into t values(1,'aaa',1);

已创建 1 行。

SQL> insert into t values(1,'aaa',1);

已创建 1 行。

SQL> insert into t values(1,'aaa',1);

已创建 1 行。

SQL> select * from t;

ID NAME DPT_ID

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

1 aaa 1

SQL> commit;

commit

ERROR 位于第 1 行:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (LUO.PK_T_ID) violated

本文转自 d185740815 51CTO博客，原文链接：http://blog.51cto.com/luotaoyang/293804，如需转载请自行联系原作者

我的Oracle 9i学习日志（18）-- 维护数据完整性.b

相关推荐