主键与唯一键的执行：
图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_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
PK_T_ID INDEX
TTABLE
#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_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
BONUSTABLE
CUSTOMERSTABLE
ORDERSTABLE
TTABLE
T_ID_IDXINDEX
UQ_EMP_IDINDEX
已选择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_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
BONUSTABLE
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_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
BONUSTABLE
UQ_EMP_ID INDEX
SQL> select constraint_name,table_name from user_constraints;
CONSTRAINT_NAMETABLE_NAME
------------------------------ ------------------------------
CK_BONUSBONUS
UQ_EMPID BONUS
SQL> alter table bonusdisable constraint uq_empid;
表已更改。
SQL> select object_name,object_type from user_objects;
OBJECT_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
BONUSTABLE
T_ID_IDXINDEX
SQL> alter table bonusenable constraint uq_empid;
表已更改。
SQL> select object_name,object_type from user_objects;
OBJECT_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
BONUSTABLE
UQ_EMPID INDEX
使用EXCEPTIONS表
•通过运行utlexcpt1.sql脚本来创建EXCEPTIONS表。
•执行带有EXCEPTIONS选项的ALTER TABLE语句。
•使用EXCEPTIONS上的子查询定位包含无效数据的行。
•纠正错误。
•重新执行ALTER TABLE以启用约束。
SQL> @/u01/oracle/rdbms/admin/utlexpt1
Table created.
SQL> desc exceptions
NameNull? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNERVARCHAR2(30)
TABLE_NAMEVARCHAR2(30)
CONSTRAINTVARCHAR2(30)
SQL> create table t(id int primary key disable,name char(10));
表已创建。
SQL> select object_name,object_type from user_objects;
OBJECT_NAMEOBJECT_TYPE
------------------------------ ------------------------------------
CUSTOMERSTABLE
EXCEPTIONSTABLE
ORDERSTABLE
TTABLE
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_IDOWNER TABLE_NAME CONSTRAINT
------------------------- ----- --------------- -------------------------
AAAHyHAANAAAAAKAABLUO T SYS_C002784
AAAHyHAANAAAAAKAACLUO T SYS_C002784
SQL> select rowid,id,name from t where rowid in
2 (select row_id from exceptions);
ROWIDID NAME
------------------------- ---------- --------------------
AAAHyHAANAAAAAKAAB2 aaa
AAAHyHAANAAAAAKAAC2 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_IDOWNER TABLE_NAME CONSTRAINT
------------------------- ----- --------------- -------------------------
AAAHyPAANAAAAAKAAALUO T SYS_C002785
AAAHyPAANAAAAAKAABLUO T SYS_C002785
AAAHyPAANAAAAAKAACLUO 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_NAMER_OWNER R_CONSTRAINT_NAME
---------- --------------- ---------- --------------------
PK_CUSTCUSTOMERS
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;
扫一扫
1199
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
