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

时间:2022-05-16 20:08:56

创建禁止性约束:

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          0 aaa          0 aaa          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   约束状态:

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

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 行。   当有大量数据处理时,一致性约束同索引一样会降低数据库性能。因此,可以按以下方法提高性能。

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

6 约束检查时间:

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

7 在约束定义完成以后,nondeferreddeferred不能相互转变。 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          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          1 aaa                                                         1          1 aaa                                                         1          1 aaa                                                         1          1 aaa                                                         1   SQL> commit; commit * ERROR 位于第 1 : ORA-02091: transaction rolled back ORA-00001: unique constraint (LUO.PK_T_ID) violated

本文出自 “OPOPOP” 博客,请务必保留此出处http://luotaoyang.blog.51cto.com/545649/293804