Unique约束,Primary Key约束与索引的关系学习与测试_20091213.doc

Unique约束,Primary Key约束与索引的关系

<> P517

<> P528

 

1 Creating an Index Associated with a Constraint

Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled. (唯一性约束和主键约束是通过建立唯一性索引来实现的，Oracle会自动建立，不必施加任何动作，但也可以通过USING  INDEX clause 来施加控制，如改变Index Storage Clause)

    A constraint’s associated index always assumes the name of the constraint, unless you optionally specify otherwise.

 

2 Specifying Storage Options for an Index Associated with a Constraint

 

CREATE TABLE emp (

empno NUMBER(5) PRIMARY KEY, age INTEGER)

ENABLE PRIMARY KEY USING INDEX

TABLESPACE users

PCTFREE 0;

 

Enable Constraints 的时候指定相应索引的存储参数

 

3 Specifying the Index Associated with a Constraint

 

Create table a (a1 int primary key using index( create index ai on a(a1)));

 

idle> Create table a (a1 int primary key using index( create index ai on t2(name)));

Create table a (a1 int primary key using index( create index ai on t2(name)))

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [kkdccui3], [], [], [], [], [], [], []

 

必须是自身表，而不能使用别的表

 

 

CREATE TABLE b(

b1 INT,

b2 INT,

CONSTRAINT bu1 UNIQUE (b1, b2)

USING INDEX (create unique index bi on b(b1, b2)),

CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

 

CREATE TABLE c(c1 INT, c2 INT);

CREATE INDEX ci ON c (c1, c2);

ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

 

4 删除index 的情况

 

显示创建的索引，显示删除，隐式建立的索引不能显式删除，只能通过禁止或者删掉约束来达到删除索引的目的.

 

idle> create table t2 (id number primary key, name varchar2(20));

 

Table created.

 

 

idle> select object_type, object_name from user_objects order by object_type;

 

OBJECT_TYPE

------------------

OBJECT_NAME

--------------------------------

SYS_C002101

 

TABLE

T1

 

TABLE

T2

 

idle> drop index SYS_C002101;

drop index SYS_C002101

           *

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

 

不能显式删除

 

idle> alter table t2 disable constraint SYS_C002101;

 

Table altered.

禁止约束

 

idle>  select object_type, object_name from user_objects order by object_type;

 

OBJECT_TYPE        OBJECT_NAME

------------------ --------------------

TABLE              T1

TABLE              T2

 

索引被删除

If a table is dropped, all associated indexes are dropped automatically.(索引依赖于表)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-622324/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-622324/