alter table add index oracle,alter table using index

最新推荐文章于 2023-11-06 15:34:27 发布

小米智能生活

最新推荐文章于 2023-11-06 15:34:27 发布

阅读量1.5k

点赞数

文章标签： alter table add index oracle

本文介绍了在Oracle中定义唯一或主键约束时如何使用索引来强制约束。可以指定已存在的索引，或者让Oracle创建索引。如果未指定索引，Oracle会创建与约束同名的索引。当尝试删除用于约束的索引时，Oracle会返回错误，因为索引被用于约束的执行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#下列指定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.