主键与唯一键的执行：
图
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，如需转载请自行联系原作者
