备案控制台
开发者社区 数据库 文章 正文

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

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

主键与唯一键的执行：

图 8
主键和唯一键通过索引执行。可控制用来执行这些约束的索引的位置和类型。
Oracle  服务器按下列步骤实现唯一键和主键约束：
•  如果约束被禁用，则不需要索引。
•  如果启用约束且约束中的列构成索引的主要部分，则无论是否将索引本身创建为唯一还是非唯一索引，都可以使用该索引执行约束。
•  如果启用约束且没有任何索引将约束列用作索引的主要部分，则按照下列规则创建一个名称与约束相同的索引：
–  如果关键字为可延迟，则在关键字列上创建一个非唯一索引。
–  如果关键字为不可延迟，则将创建一个唯一索引。
•  如果可以使用某个索引，并且约束是不可延迟的，则使用现有索引。如果约束是可延迟的，并且索引是非唯一的，则使用现有索引。如果存在非唯一性索引，则不能创建可延迟的主键或唯一键约束。
SQL> drop index pk_t_id;
drop index pk_t_id
           *
ERROR  位于第  1  行 :
ORA-02429: cannot drop index used for enforcement of unique/primary key
 
 
SQL> alter table t drop primary key;
 
表已更改。
 
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
PK_T_ID                        INDEX
T                              TABLE
#drop 约束后，索引依然存在。
已选择 6 行。
 
SQL> drop index pk_t_id;
 
索引已丢弃。
 
SQL> delete from t;
 
已删除 2 行。
 
SQL> create unique index t_id_idx 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_IDX                       INDEX
UQ_EMP_ID                      INDEX
 
已选择 6 行。
 
SQL> alter table t add constraint pk_t_id primary key(id) deferrable;
alter table t add constraint pk_t_id primary key(id) deferrable
*
ERROR  位于第  1  行 :
ORA-01408: such column list already indexed
外键注意事项：

图 9
维护外键关系中的表时，应该考虑几个因素。
涉及父表的 DDL ：
在删除父表之前，必须先删除外键。可以使用以下一条语句同时执行这两个操作：
DROP TABLE table CASCADE CONSTRAINTS
在未删除或禁用外键之前无法截断父表。
在删除包含父表的表空间之前，必须先删除外键。可使用下列命令完成该操作：
DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
如果从父表中删除行时没有使用 DELETE CASCADE  选项， Oracle  服务器必须确保子表中的行不包含相应的外键。同样，仅当子行中不包含旧键值时，才允许更新父键。如果子表的外键上没有索引，则 Oracle  服务器锁定子表并禁止更改以确保引用完整性。如果表上有索引，则可通过锁定索引项并避免子表上有更具限制性的锁来维护引用完整性。如果必须从不同的事务处理同时更新两个表，则在外键列上创建索引。当在子表中插入数据或更新子表中的外键列时， Oracle  服务器检查父表上用来执行引用关键字的索引。因此，仅当包含索引的表空间联机时，该操作才能成功。注意，包含父表的表空间在子表上执行 DML  操作时不需要联机。
Oracle9i  在主键上执行更新或删除操作时，不再要求在未建索引的外键上获取共享锁定。它仍然获取表级共享锁定，但在获取后立即释放该锁定。如果更新或删除多个主键，则每行获取和释放一次锁定。

图 10
子表外键不建立索引时，如果更新父表则会引起整个表被锁住。

图 11
子表外键建立索引，更新父表只会锁住行。
创建表时定义索引：
CREATE TABLE hr.employee(
id NUMBER(7)
CONSTRAINT employee_id_pk PRIMARY KEY
DEFERRABLE
USING INDEX
STORAGE(INITIAL 100K NEXT 100K)
TABLESPACE indx,
last_name VARCHAR2(25)
CONSTRAINT employee_last_name_nn NOT NULL,
dept_id NUMBER(7))
TABLESPACE users;
可以在创建或改变表时定义约束。使用 CREATE TABLE  或 ALTER TABLE  语句中的 constraint_clause  子句来定义约束。要定义完整性约束，必须具有所需的权限。要创建引用完整性约束，父表必须位于您自己的方案中，或者您必须对父表中的引用键列拥有 REFERENCES  权限。
column_constraint  语法是表定义的一部分。在创建表时，可以使用以下语法定义约束：
column datatype [CONSTRAINT constraint]
{[NOT] NULL
|UNIQUE [USING INDEX index_clause]
|PRIMARY KEY [USING INDEX index_clause]
|REFERENCES [schema.]table [(column)]
[ON DELETE CASCADE]
|CHECK (condition)
}
constraint_state :==
[NOT DEFERRABLE|DEFERRABLE [INITIALLY
{IMMEDIATE|DEFERRED}]
]
[DISABLE|ENABLE [VALIDATE|NOVALIDATE]]
其中：
CONSTRAINT ：使用存储在数据字典中的名称 constraint  来标识完整性约束 USING INDEX ：指定将 index-clause  中定义的参数用于 Oracle  服务器使用的索引，以执行唯一键约束或主键约束（索引的名称与约束的名称相同。） DEFERRABLE ：表示可使用 SET CONSTRAINT  命令将约束检查延迟到事务处理结束时 NOT DEFERRABLE ：表示在每一 DML  语句结束时检查该约束（会话或事务处理不能延迟 NOT DEFERRABLE  约束。 NOT DEFERRABLE  是缺省值。） INITIALLY IMMEDIATE ：表示在每一事务处理开始时，缺省为在每一 DML  语句结束时检查该约束（如果没有指定子句 INITIALLY ，则缺省情况下为 INITIALLY IMMEDIATE 。） INITIALLY DEFERRED ：表示该约束为 DEFERRABLE ，并指定缺省时只在每一事务处理结束时检查该约束 DISABLE ：禁用完整性约束（如果禁用完整性约束，则 Oracle  服务器不执行该约束。）
表约束：
表约束是表定义的一部分。它可以定义除 NOT NULL  约束以外的任何约束。表约束是使用以下语法定义的：
[CONSTRAINT constraint]
{PRIMARY KEY (column [, column ]... )
[USING INDEX index_clause]
|UNIQUE (column [, column ]... )
[USING INDEX index_clause]
|FOREIGN KEY (column [, column ]... )
REFERENCES [schema.]table [(column [, column ]... )]
[ON DELETE CASCADE]
|CHECK (condition)
}
[constraint_state]
 
•  采用约束的标准命名约定是一个好习惯。这对 CHECK  约束更是如此，因为可使用不同的名称多次创建同一约束。
•  下列情形需要使用表约束：
–  当约束命名两列或更多列时
–  改变表以添加除 NOT NULL  约束外的约束时
•  要在创建表后从类型 NOT NULL  定义约束，只能使用以下语句：
ALTER TABLE table MODIFY column CONSTRAINT constraint NOT NULL;
创建表后定义约束：示例
SQL> ALTER TABLE hr.employee
2 ADD(CONSTRAINT employee_dept_id_fk FOREIGN KEY(dept_id)
3 REFERENCES hr.department(id)
4 DEFERRABLE INITIALLY DEFERRED);
约束定义原则
定义约束时遵循下列原则十分有用：
•  将用于执行主键约束和唯一性约束的索引与表放在不同的表空间中。这可通过指定
USING INDEX  子句或通过创建表、创建索引并改变表以添加或启用约束来实现。
•  如果经常向表中批量加载数据，则最好先禁用约束，执行完加载后再启用约束。如果
唯一索引用于执行主键约束或唯一性约束，则在禁用约束时必须删除该索引。在这种
情况下，可以使用非唯一索引执行主键约束或唯一性约束来改善性能：创建可延迟的
键，或者在定义或启用键之前创建索引。
•  如果表中包含自引用外键，请使用下列方法之一加载数据：
–  在初始加载后定义或启用外键。
–  将约束定义为可延迟的约束。
在频繁加载数据的情况下，第二种方法非常有用。
启用索引：

图 12
可以采用下列两种方法之一来启用当前禁用的约束： ENABLE NOVALIDATE  或 ENABLE VALIDATE
启用 NOVALIDATE:
对于当前已有索引的 PRIMARY KEY  和 UNIQUE  约束，启用 NOVALIDATE  约束比启用 VALIDATE  约束要快得多，这是因为，如果约束是可延迟的，则不检查现有数据是否违反约束。如果使用该选项启用约束，则不要求锁定表。这种方法适合表上有许多 DML  活动的情况，如在 OLTP  环境中。
下列命令可用于启用 ENABLE NOVALIDATE  约束：
ALTER TABLE [ schema. ] table
ENABLE NOVALIDATE {CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
限制：
USING INDEX  子句仅适用于创建为可延迟的主键约束或唯一性约束，并且下列条件之一为真的情况：
•  约束被创建为禁用。
•  约束被禁用且索引已删除。
但是，如果需要创建索引，使用这种启用约束的方法并不能比 ENABLE VALIDATE  带来
更多的好处，因为 Oracle  服务器在建立索引时锁定表。
启用约束:
启用 VALIDATE  约束后将检查现有数据中是否违反约束。这是启用约束时的缺省操作。若在禁用约束时执行，则会产生下列影响：
•  锁定表，以防在验证完现有数据前对表进行更改。但在 9i 及以后的版本中从 enable novalidate 状态切换到 enable validate 状态也可不锁表。
•  如果索引列上不存在索引， Oracle  服务器就会创建一个索引。当启用不可延迟的主键约束或唯一性约束时， Oracle  服务器将创建一个唯一索引。对于可延迟的主键约束或唯一性约束，将建立一个非唯一索引。
如果在执行约束时执行此命令，则不要求在验证过程中锁定任何表。执行的约束将保证在验证期间不会出现违反约束的情况。这有如下好处：
•  所有约束并发启用。
•  每一约束在内部保持并行。
•  允许表上存在并发操作。
使用以下命令启用约束 ENABLE VALIDATE ：
ALTER TABLE [ schema. ] table
ENABLE [ VALIDATE ]{CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
[ EXCEPTIONS INTO [ schema. ] table ]
注：
• VALIDATE  选项为缺省设置，不需要在启用被禁用约束时指定。
•  如果表中的数据违反约束，则语句回退，约束仍被禁用。
重命名约束：
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
BONUS                          TABLE
UQ_EMP_ID                      INDEX
 
SQL> alter table bonus rename constraint uq_emp_id to uq_empid;
 
表已更改。
 
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
BONUS                          TABLE
UQ_EMP_ID                      INDEX
 
SQL> select constraint_name,table_name from user_constraints;
 
CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
CK_BONUS                       BONUS
UQ_EMPID                       BONUS
 
SQL> alter table bonus disable constraint uq_empid;
 
表已更改。
 
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
BONUS                          TABLE
T_ID_IDX                       INDEX
 
SQL> alter table bonus enable constraint uq_empid;
 
表已更改。
 
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
BONUS                          TABLE
UQ_EMPID                       INDEX
使用EXCEPTIONS  表
•  通过运行 utlexcpt1.sql  脚本来创建 EXCEPTIONS 表。
•  执行带有 EXCEPTIONS  选项的 ALTER TABLE  语句。
•  使用 EXCEPTIONS  上的子查询定位包含无效数据的行。
•  纠正错误。
•  重新执行 ALTER TABLE  以启用约束。
SQL> @/u01/oracle/rdbms/admin/utlexpt1
 
Table created.
 
SQL> desc exceptions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                            ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)
SQL> create table t(id int primary key disable,name char(10));
 
表已创建。
 
SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------------
CUSTOMERS                      TABLE
EXCEPTIONS                     TABLE
ORDERS                         TABLE
T                              TABLE
 
SQL> insert into t values(1,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(2,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(2,'aaa');
 
已创建  1  行。
 
SQL> commit
 2 ;
 
提交完成。
 
SQL> select * from t;
 
        ID NAME
---------- --------------------
         1 aaa
         2 aaa
         2 aaa
 
SQL> alter table t enable primary key ;
alter table t enable primary key
*
ERROR  位于第  1  行 :
ORA-02437: cannot validate (LUO.SYS_C002784) - primary key violated
 
 
SQL> alter table t enable primary key exceptions into exceptions;
alter table t enable primary key exceptions into exceptions
*
ERROR  位于第  1  行 :
ORA-02437: cannot validate (LUO.SYS_C002784) - primary key violated
SQL> select * from exceptions;
 
ROW_ID                    OWNER TABLE_NAME      CONSTRAINT
------------------------- ----- --------------- -------------------------
AAAHyHAANAAAAAKAAB        LUO   T               SYS_C002784
AAAHyHAANAAAAAKAAC        LUO   T               SYS_C002784
SQL> select rowid,id,name from t where rowid in
 2 (select row_id from exceptions);
 
ROWID                             ID NAME
------------------------- ---------- --------------------
AAAHyHAANAAAAAKAAB                 2 aaa
AAAHyHAANAAAAAKAAC                 2 aaa
# 在下次使用 exceptions 表前必须先清空或 truncate 。否则以前的结果会依然保留在表里，影响结果的查询。
注意：在启用约束前， Oracle 会自动 commit 事务。如下所示：
SQL> create table t(id int primary key disable,name char(10));
 
表已创建。
 
SQL> insert into t values(1,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(1,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(1,'aaa');
 
已创建  1  行。
 
SQL> rollback;
 
回退已完成。
 
SQL> select * from t;
 
未选定行
 
SQL> insert into t values(2,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(2,'aaa');
 
已创建  1  行。
 
SQL> insert into t values(2,'aaa');
 
已创建  1  行。
 
SQL> alter table t enable primary key exceptions into exceptions;
alter table t enable primary key exceptions into exceptions
*
ERROR  位于第  1  行 :
ORA-02437: cannot validate (LUO.SYS_C002785) - primary key violated
 
 
SQL> rollback;
 
回退已完成。
 
SQL> select * from t;
 
        ID NAME
---------- --------------------
         2 aaa
         2 aaa
         2 aaa
 
SQL> select * from exceptions;
 
ROW_ID                    OWNER TABLE_NAME      CONSTRAINT
------------------------- ----- --------------- -------------------------
AAAHyPAANAAAAAKAAA        LUO   T               SYS_C002785
AAAHyPAANAAAAAKAAB        LUO   T               SYS_C002785
AAAHyPAANAAAAAKAAC        LUO   T               SYS_C002785
获取信息：
• DBA_CONSTRAINTS
• DBA_CONS_COLUMNS
在 DBA_CONSTRAINTS 中：

图 13
SQL> alter table orders add constraint fk_ord_cust foreign key(cust_code)
 2 references customers(cust_code);
references customers(cust_code)
                     *
ERROR  位于第  2  行 :
ORA-02270: no matching unique or primary key for this column-list
 
SQL> alter table customers add constraint pk_cust primary key(cust_code);
 
表已更改。
 
SQL> alter table orders add constraint fk_ord_cust foreign key(cust_code)
 2 references customers(cust_code);
 
表已更改。
 
SQL> select object_name from user_objects;
 
OBJECT_NAME
------------------------------
CUSTOMERS
EXCEPTIONS
ORDERS
PK_CUST
 
SQL> select constraint_name,table_name,r_owner,r_constraint_name from user_const
raints;
 
CONSTRAINT TABLE_NAME      R_OWNER    R_CONSTRAINT_NAME
---------- --------------- ---------- --------------------
PK_CUST    CUSTOMERS
FK_ORD_CUST ORDERS          LUO        PK_CUST
要获得 HR  的 EMPLOYEE  表内约束中的列，使用下列查询：
SQL> SELECT c.constraint_name, c.constraint_type,
2 cc.column_name
3 FROM dba_constraints c, dba_cons_columns cc
4 WHERE c.owner='HR'
5 AND c.table_name='EMPLOYEE'
6 AND c.owner = cc.owner
7 AND c.constraint_name = cc.constraint_name
8 ORDER BY cc.position;
要查找 HR  的 EMPLOYEE  表上的外键和父约束，请使用下列查询：
SQL> SELECT c.constraint_name AS "Foreign Key",
2 p.constraint_name AS "Referenced Key",
3 p.constraint_type,
4 p.owner,
5 p.table_name
6 FROM dba_constraints c, dba_constraints p
7 WHERE c.owner='HR'
8 AND c.table_name='EMPLOYEE'
9 AND c.constraint_type='R'
10 AND c.r_owner=p.owner
11 AND c.r_constraint_name = p.constraint_name;









本文转自 d185740815 51CTO博客，原文链接：http://blog.51cto.com/luotaoyang/293805，如需转载请自行联系原作者
文章标签：
日志服务
索引
关系型数据库
SQL
监控
Oracle
关键词：
日志服务数据
Oracle数据库一键上云日志
Oracle数据库一键上云数据
日志服务oracle
日志服务学习
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
余二五
目录
相关文章
熊猫Jay节字之旅
|
19天前
|
人工智能 数据可视化 开发工具
Git log 进阶用法（含格式化、以及数据过滤）
Git log 进阶用法（含格式化、以及数据过滤）
熊猫Jay节字之旅
20 0
宁波阿成.
|
4天前
|
SQL Oracle 关系型数据库
oracle11g SAP测试机归档日志暴增排查（二）
oracle11g SAP测试机归档日志暴增排查（二）
宁波阿成.
13 1
宁波阿成.
|
4天前
|
Oracle 关系型数据库 Shell
oracle11g SAP测试机归档日志暴增排查（一）
oracle11g SAP测试机归档日志暴增排查（一）
宁波阿成.
11 1
yuanzhengme
|
8天前
|
监控 NoSQL MongoDB
mongoDB查看数据的插入日志
【5月更文挑战第2天】mongoDB查看数据的插入日志
yuanzhengme
34 0
yuanzhengme
|
2天前
|
监控 NoSQL MongoDB
mongoDB查看数据的插入日志
【5月更文挑战第9天】mongoDB查看数据的插入日志
yuanzhengme
15 4
神秘泣男子
|
8天前
|
关系型数据库 MySQL 数据管理
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
MySQL通过 bin-log 恢复从备份点到灾难点之间数据
神秘泣男子
21 0
小谢在努力
|
15天前
|
SQL 监控 关系型数据库
【MySQL学习】MySQL的慢查询日志和错误日志
【MySQL学习】MySQL的慢查询日志和错误日志
小谢在努力
11 1
拓端数据部落
|
20天前
|
机器学习/深度学习 前端开发 数据挖掘
工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断（下）
工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断
拓端数据部落
223 11
TesterMuller
|
20天前
|
监控 Docker 容器
Docker从入门到精通：Docker log 命令学习
了解 Docker 日志管理对容器监控至关重要。`docker logs` 命令用于查看和管理容器日志，例如，`docker logs <container_name>` 显示容器日志，`-f` 或 `--follow` 实时跟踪日志，`--tail` 显示指定行数，`--timestamps` 添加时间戳，`--since` 按日期筛选。Docker 支持多种日志驱动，如 `syslog`，可通过 `--log-driver` 配置。有效管理日志能提升应用程序的稳定性和可维护性。
TesterMuller
17 0
xiaowang_lj
|
7天前
|
C++
JNI Log 日志输出
JNI Log 日志输出
xiaowang_lj
15 1

热门文章

最新文章

  • 1
    [MySQL]事务原理之redo log,undo log
  • 2
    在云效中，如果你看不到错误日志，可能是因为错误日志没有被正确地输出或者存储
  • 3
    Redis内核基于时间点的备份恢复和基于AOF日志的增量同步机制设计
  • 4
    iLogtail社区版使用入门 - 如何将业务日志采集到Kafka
  • 5
    Server Down log
  • 6
    用Windows Live Writer发布日志到BlogBus
  • 7
    jackson error 含义log
  • 8
    log file sync总结
  • 9
    mysql 利用二进制日志进行数据恢复(mysqlbinlog)
  • 10
    秋式网站日志分析器[IISLogViewer] V3版本发布
  • 1
    服务器Centos7 静默安装Oracle Database 12.2
    116
  • 2
    SpringBoot整合Mybatis连接Oracle数据库
    75
  • 3
    避坑，Oracle中rownum与order by的执行顺序
    31
  • 4
    避坑，Oracle中left join 与 (+) 的区别
    31
  • 5
    Oracle临时表详解
    23
  • 6
    Oracle insert数据时字符串中有‘单引号问题
    57
  • 7
    【Bug记录】Oracle中小数点前面的0不显示的问题
    67
  • 8
    Oracle中merge Into的用法
    18
  • 9
    Flink CDC产品常见问题之使用cdc-Oracle连接器报错如何解决
    64
  • 10
    Oracle系列十八：Oracle RAC
    68

    • 相关课程

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

    • 相关电子书

    更多
  • PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
  • Kubernetes下日志实时采集、存储与计算实践
  • 日志数据采集与分析对接

    • 相关实验场景

    更多
  • 通过日志服务实现云资源OSS的安全审计
  • 如何将OSS数据导入至SLS中进行分析
  • 日志服务之使用Nginx模式采集日志
  • Tair for Redis数据闪回：任意时间点数据恢复
  • 使用DAS实现数据库自动SQL优化

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）