#下列指定using index的行為,用於新增主鍵約束時,如果索引存在,就把索引與主鍵關聯;
也可以指定建立索引的語句,或者不指定具體的索引,如果不存在,會預設建立與主鍵約束同名的索引
Using Indexes to Enforce Constraints
When defining the state of a unique or primary key constraint, you can specify an index for Oracle to use to enforce the constraint, or you can instruct Oracle to create the index used to enforce the constraint.

using_index_clause
You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

•If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle returns an error.

•If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, then Oracle returns an error.

•If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

◦The index receives the same name as the constraint.

◦If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

SQL> desc t_using;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)

SQL> alter table t_using add b int;

Table altered.

SQL> alter table t_using add constraint
2 con_uni_x unique(b) using index tablespace zxy;#利用using index並指定索引儲存在表空間zxy中

Table altered.

SQL> select index_name from user_indexes where table_name='T_USING';

INDEX_NAME
------------------------------
CON_UNI_X
CON_UNI

SQL> drop index con_uni_x;
drop index con_uni_x
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>

alter table using index

相關文章