维护数据完整性
目的
完成本课后，可以做下列事情：
实施数据完整性约束
维护完整性约束
取得完整性约束信息
维护包含外键约束的表，要考虑下面几个因素：
删除父表前，必须先删除外键约束。使用下面语句可以同时删除外键
DROP TABLE table CASCADE CONSTRAINTS
如果没有删除或者禁用外键，那么不能截断父表
要删除包含父表的表空间，要使用下面命令，否则不能删除：
DROP TABLESPACE tablespace INCLUDING CONTENTS
CASCADE CONSTRAINTS
如果删除行没有使用DELETE CASCADE选项，那么要删除的行必须在子表中没有对应项。更新也类似。如果子表的外键上面没有索引，那么进行
上面操作时Oracle服务器会锁定子表防止改变以保证完整性。如果有索引，则只锁定索引，从而避免更多的限制锁。如果两个表在不同的事务
里面同时更新，那么要在外键上面创建索引。
如果子表上面要插入行或者是更新外键上的值，Oracle服务器会检查父表上外键的索引以保证完整性，因此命令只有在包含索引的表空间在线
的时候才能成功。注意，在子表上面执行DML操作不需要包含父表的表空间在线。
Oracle9i 在主键上执行更新或删除操作时，不再要求在未建索引的外键上获取共享锁定。它仍然获取表级共享锁定，但在获取后立即释放该锁
定。如果更新或删除多个主键，则每行获取和释放一次锁定。
要创建完整性约束，必须具有相应的权限。对于参考完整性约束，父表必须使自己的方案对象，或者具有参考键上面的参考权限。
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]]
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]
下列情况需要表级约束：
当约束命名两列或更多列时
改变表以添加除 NOT NULL 约束外的约束时
要在创建表后从类型 NOT NULL 定义约束，只能使用以下语句：
ALTER TABLE table MODIFY column CONSTRAINT constraint NOT NULL;
定义约束指南
将用于执行主键约束和唯一性约束的索引与表放在不同的表空间中。这可通过指定 USING INDEX 子句或通过创建表、创建索引并改变表以添加
或启用约束来实现。
如果经常向表中批量加载数据，则最好先禁用约束，执行完加载后再启用约束。如果唯一索引用于执行主键约束或唯一性约束，则在禁用约束
时必须删除该索引。在这种情况下，可以使用非唯一索引执行主键约束或唯一性约束来改善性能：创建可延迟的键，或者在定义或启用键之前
创建索引。
如果表中包含自引用外键，请使用下列方法之一加载数据：
在初始加载后定义或启用外键。
将约束定义为可延迟的约束。
在频繁加载数据的情况下，第二种方法非常有用。
可以有两种方法启用索引
ENABLE NOVALIDATE or ENABLE VALIDATE
对于已经存在索引的主键约束或者唯一约束，启用novalidate约束要远远快于启用validate约束，因为已有的数据并不进行完整性检查，如果
约束是可以延迟的。如果使用该选项启用约束，则不要求锁定表。这种方法适合表上有许多 DML 活动的情况，如在 OLTP 环境中。
ALTER TABLE [ schema. ] table
ENABLE NOVALIDATE {CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
限制：
只应该用于下列情况 -- 约束是可延迟的，并且满足下面之一：
约束创建时为禁用状态
约束为禁用状态，索引被删除
但是，如果需要创建索引，使用这种启用约束的方法并不能比 ENABLE VALIDATE 带来更多的好处，因为 Oracle 服务器在建立索引时锁定表。
启用validate约束会检查已有数据，这将导致表被锁定并且知道完整性检测完之后才能改变/更新表
如果不存在索引，将创建索引。立即主键约束和立即唯一约束将导致创建唯一性索引。可延迟的主键约束和唯一约束则创建非唯一索引。
有下列优点：
所有约束并发启用。
每一约束在内部保持并行。
允许表上存在并发操作。
ALTER TABLE [ schema. ] table
ENABLE [ VALIDATE ]{CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
[ EXCEPTIONS INTO [ schema. ] table ]
VALIDATE 选项为缺省设置，不需要在启用被禁用约束时指定。
如果表中的数据违反约束，则语句回退，约束仍被禁用。
使用EXCEPTIONS表
使用下面步骤检测不合法的数据、校正然后重新启用约束
SQL> @?/rdbms/admin/utlexcpt1 -- 创建exceptions表
Statement processed.
SQL> DESCRIBE exceptions
SQL> ALTER TABLE hr.employee -- 注意带exceptions子句
2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
3 EXCEPTIONS INTO system.exceptions;
SQL> SELECT rowid, id, last_name, dept_id
2 FROM hr.employee
3 WHERE ROWID in (SELECT row_id
4 FROM exceptions)
5 FOR UPDATE;
SQL> UPDATE hr.employee
2 SET id=10
3 WHERE rowid=’AAAAeyAADAAAAA1AAA’;
SQL> COMMIT;
SQL> TRUNCATE TABLE exceptions;
SQL> ALTER TABLE hr.employee
2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
3 EXCEPTIONS INTO system.exceptions;
获得约束信息
DBA_CONSTRATINTS, DBA_CONS_COLUMNS
SQL> SELECT constraint_name, constraint_type, deferrable,
2 deferred, validated
3 FROM dba_constraints
4 WHERE owner=’HR’
5 AND table_name=’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;
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;
名称 说明
CONSTRAINT_TYPE 如果为主键约束，则约束类型为 P；如果为唯一性约束，则为 U；如果为外键约束，则为 R；如果为检查约束， 则为 C。
NOT NULL 约束存储为检查约束。
SEARCH_CONDITION 显示为检查约束指定的条件
R_OWNER
R_CONSTRAINT_NAME 为外键定义引用约束的所有者和名称
GENERATED 指示约束名是否由系统生成（有效值为 USERNAME 和 GENERATED NAME。）
BAD 指示将重写约束以避免千年虫等问题
RELY 如果设置此标志，则此标志用于优化程序
LAST_CHANGE 显示上次启用或禁用约束的日期
本文转自
http://kongjian.baidu.com/xu520zl/blog/item/b1da5cf30da12a52342acc67.html
目的
完成本课后，可以做下列事情：
实施数据完整性约束
维护完整性约束
取得完整性约束信息
维护包含外键约束的表，要考虑下面几个因素：
删除父表前，必须先删除外键约束。使用下面语句可以同时删除外键
DROP TABLE table CASCADE CONSTRAINTS
如果没有删除或者禁用外键，那么不能截断父表
要删除包含父表的表空间，要使用下面命令，否则不能删除：
DROP TABLESPACE tablespace INCLUDING CONTENTS
CASCADE CONSTRAINTS
如果删除行没有使用DELETE CASCADE选项，那么要删除的行必须在子表中没有对应项。更新也类似。如果子表的外键上面没有索引，那么进行
上面操作时Oracle服务器会锁定子表防止改变以保证完整性。如果有索引，则只锁定索引，从而避免更多的限制锁。如果两个表在不同的事务
里面同时更新，那么要在外键上面创建索引。
如果子表上面要插入行或者是更新外键上的值，Oracle服务器会检查父表上外键的索引以保证完整性，因此命令只有在包含索引的表空间在线
的时候才能成功。注意，在子表上面执行DML操作不需要包含父表的表空间在线。
Oracle9i 在主键上执行更新或删除操作时，不再要求在未建索引的外键上获取共享锁定。它仍然获取表级共享锁定，但在获取后立即释放该锁
定。如果更新或删除多个主键，则每行获取和释放一次锁定。
要创建完整性约束，必须具有相应的权限。对于参考完整性约束，父表必须使自己的方案对象，或者具有参考键上面的参考权限。
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]]
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]
下列情况需要表级约束：
当约束命名两列或更多列时
改变表以添加除 NOT NULL 约束外的约束时
要在创建表后从类型 NOT NULL 定义约束，只能使用以下语句：
ALTER TABLE table MODIFY column CONSTRAINT constraint NOT NULL;
定义约束指南
将用于执行主键约束和唯一性约束的索引与表放在不同的表空间中。这可通过指定 USING INDEX 子句或通过创建表、创建索引并改变表以添加
或启用约束来实现。
如果经常向表中批量加载数据，则最好先禁用约束，执行完加载后再启用约束。如果唯一索引用于执行主键约束或唯一性约束，则在禁用约束
时必须删除该索引。在这种情况下，可以使用非唯一索引执行主键约束或唯一性约束来改善性能：创建可延迟的键，或者在定义或启用键之前
创建索引。
如果表中包含自引用外键，请使用下列方法之一加载数据：
在初始加载后定义或启用外键。
将约束定义为可延迟的约束。
在频繁加载数据的情况下，第二种方法非常有用。
可以有两种方法启用索引
ENABLE NOVALIDATE or ENABLE VALIDATE
对于已经存在索引的主键约束或者唯一约束，启用novalidate约束要远远快于启用validate约束，因为已有的数据并不进行完整性检查，如果
约束是可以延迟的。如果使用该选项启用约束，则不要求锁定表。这种方法适合表上有许多 DML 活动的情况，如在 OLTP 环境中。
ALTER TABLE [ schema. ] table
ENABLE NOVALIDATE {CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
限制：
只应该用于下列情况 -- 约束是可延迟的，并且满足下面之一：
约束创建时为禁用状态
约束为禁用状态，索引被删除
但是，如果需要创建索引，使用这种启用约束的方法并不能比 ENABLE VALIDATE 带来更多的好处，因为 Oracle 服务器在建立索引时锁定表。
启用validate约束会检查已有数据，这将导致表被锁定并且知道完整性检测完之后才能改变/更新表
如果不存在索引，将创建索引。立即主键约束和立即唯一约束将导致创建唯一性索引。可延迟的主键约束和唯一约束则创建非唯一索引。
有下列优点：
所有约束并发启用。
每一约束在内部保持并行。
允许表上存在并发操作。
ALTER TABLE [ schema. ] table
ENABLE [ VALIDATE ]{CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE ( column [, column ] ... ) }
[ USING INDEX index_clause ]
[ EXCEPTIONS INTO [ schema. ] table ]
VALIDATE 选项为缺省设置，不需要在启用被禁用约束时指定。
如果表中的数据违反约束，则语句回退，约束仍被禁用。
使用EXCEPTIONS表
使用下面步骤检测不合法的数据、校正然后重新启用约束
SQL> @?/rdbms/admin/utlexcpt1 -- 创建exceptions表
Statement processed.
SQL> DESCRIBE exceptions
SQL> ALTER TABLE hr.employee -- 注意带exceptions子句
2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
3 EXCEPTIONS INTO system.exceptions;
SQL> SELECT rowid, id, last_name, dept_id
2 FROM hr.employee
3 WHERE ROWID in (SELECT row_id
4 FROM exceptions)
5 FOR UPDATE;
SQL> UPDATE hr.employee
2 SET id=10
3 WHERE rowid=’AAAAeyAADAAAAA1AAA’;
SQL> COMMIT;
SQL> TRUNCATE TABLE exceptions;
SQL> ALTER TABLE hr.employee
2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
3 EXCEPTIONS INTO system.exceptions;
获得约束信息
DBA_CONSTRATINTS, DBA_CONS_COLUMNS
SQL> SELECT constraint_name, constraint_type, deferrable,
2 deferred, validated
3 FROM dba_constraints
4 WHERE owner=’HR’
5 AND table_name=’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;
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;
名称 说明
CONSTRAINT_TYPE 如果为主键约束，则约束类型为 P；如果为唯一性约束，则为 U；如果为外键约束，则为 R；如果为检查约束， 则为 C。
NOT NULL 约束存储为检查约束。
SEARCH_CONDITION 显示为检查约束指定的条件
R_OWNER
R_CONSTRAINT_NAME 为外键定义引用约束的所有者和名称
GENERATED 指示约束名是否由系统生成（有效值为 USERNAME 和 GENERATED NAME。）
BAD 指示将重写约束以避免千年虫等问题
RELY 如果设置此标志，则此标志用于优化程序
LAST_CHANGE 显示上次启用或禁用约束的日期
本文转自
http://kongjian.baidu.com/xu520zl/blog/item/b1da5cf30da12a52342acc67.html
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
