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.(索引依賴於表)

Unique約束,Primary Key約束與索引的關係學習與測試_20091213.doc

相關文章