备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2017-11-23 880
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

创建禁止性约束：

• 加 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
 
约束状态：

图 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
         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









本文转自 d185740815 51CTO博客，原文链接：http://blog.51cto.com/luotaoyang/293804，如需转载请自行联系原作者
文章标签：
日志服务
云数据库 ClickHouse
索引
关系型数据库
SQL
监控
Oracle
关键词：
日志服务数据
Oracle数据库一键上云数据
Oracle数据库一键上云日志
日志服务oracle
Oracle数据库一键上云完整性
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
余二五
目录
相关文章
陈明勇
|
3月前
|
存储 Go
Go 浅析主流日志库：从设计层学习如何集成日志轮转与切割功能
本文将探讨几个热门的 go 日志库如 logrus、zap 和官网的 slog，我将分析这些库的的关键设计元素，探讨它们是如何支持日志轮转与切割功能的配置。
陈明勇
93 0
Go 浅析主流日志库：从设计层学习如何集成日志轮转与切割功能
vohelon
|
4月前
|
SQL 关系型数据库 MySQL
我使用flinkcdc的sql形式进行全量同步，4张表，有两张表数据没进去，看日志，id怎么是null呢?
我使用flinkcdc的sql形式进行全量同步，4张表，有两张表数据没进去，看日志，id怎么是null呢?
vohelon
116 40
lady_killer9
|
4月前
|
Kubernetes 容器
k8s学习-CKS真题-日志审计 log audit
k8s学习-CKS真题-日志审计 log audit
lady_killer9
73 0
流烟默
|
4月前
|
存储 缓存 关系型数据库
认真学习MySQL的事务日志-Redo日志
认真学习MySQL的事务日志-Redo日志
流烟默
44 0
lady_killer9
|
4月前
|
Kubernetes 监控 容器
k8s学习-CKA真题-监控Pod日志
k8s学习-CKA真题-监控Pod日志
lady_killer9
78 0
红目香薰
|
2月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
红目香薰
45 0
.29.
|
4月前
|
SQL 关系型数据库 MySQL
⑩⑥ 【MySQL】详解 触发器TRIGGER，协助 确保数据的完整性，日志记录，数据校验等操作。
⑩⑥ 【MySQL】详解 触发器TRIGGER，协助 确保数据的完整性，日志记录，数据校验等操作。
.29.
37 0
流烟默
|
4月前
|
SQL 存储 关系型数据库
认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）
认真学习MySQL中的二进制日志(binlog)与中继日志（Relay log）
流烟默
97 0
SarPro
|
8天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
SarPro
45 7
wljslmz
|
1月前
|
Oracle 关系型数据库 数据库
在 Docker 中运行 Oracle 数据库容器详细教程
【2月更文挑战第21天】
wljslmz
163 2

热门文章

最新文章

  • 1
    Oracle 面试题及答案整理，最新面试题
  • 2
    Seata常见问题之oracle 数据库 报 just support mysql如何解决
  • 3
    PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
  • 4
    Dataphin实时集成Oracle CDC相关问题排查
  • 5
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 6
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 7
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 8
    更优性能与性价比，从自建 ELK 迁移到 SLS 开始
  • 9
    struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
  • 10
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 1
    [MySQL]事务原理之redo log,undo log
    41
  • 2
    在云效中，如果你看不到错误日志，可能是因为错误日志没有被正确地输出或者存储
    13
  • 3
    Windows环境下面启动jar包，输出的日志出现乱码的解决办法
    46
  • 4
    Flink CDC产品常见问题之sql运行中查看日志任务失败如何解决
    30
  • 5
    全新架构！日志服务 SLS 自研免登录方案发布
    87434
  • 6
    Loguru：Python中强大的日志库
    40
  • 7
    使用 SPL 高效实现 Flink SLS Connector 下推
    56028
  • 8
    log4j没在根目录下如何解决
    17
  • 9
    二叉树的前序遍历 、二叉树的最大深度、平衡二叉树、二叉树遍历【LeetCode刷题日志】
    18
  • 10
    反转链表、链表的中间结点、合并两个有序链表【LeetCode刷题日志】
    22

    • 相关课程

    更多
  • 日志服务SLS实现云产品可观测
  • 日志服务 SLS 可观测数据分析平台介绍
  • 大数据知识图谱系列—基于ELK+Flink日志全观测最佳实践
  • 场景实践-基于阿里云Quick BI 对MOOC网站日志分析
  • 数据库核心概念

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用CloudLens管理SLS日志采集
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • 使用CloudLens采集RDS日志并进行审计分析
  • 基于SLS快速实现业务可观测
  • 使用日志服务SLS进行OSS可观测分析
  • PolarDB的Online DDL功能验证实验

    • 推荐镜像

    更多
  • oracle
  • arch4edu
  • debian-archive
    • 下一篇
    阿里云oss简介和使用流程