Oracle 唯一 约束(unique constraint) 与 索引(index) 关系说明

el/2024/4/19 23:22:45

 

一. 官网对Unique Constraints说明

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1642

 

       uniquekey constraint requires that every value in a column or set of columns beunique. No rows of a table may have duplicate values in a column (the uniquekey) or set of columns (the composite unique key) with a unique key constraint.

 

Note:

       Theterm key refers only to the columns defined in the integrity constraint. Because the database enforces a unique constraint byimplicitly creating or reusing an index on the key columns, the term uniquekey is sometimes incorrectly used as a synonym for unique key constraint orunique index.

       --数据库在创建unique constraint的同时,强制创建或者重用列上的索引。如果之前列上没有索引,那么强制创建的索引是unique index,如果列上已经存在索引,就重用之前的索引。

 

       Uniquekey constraints are appropriate for any column where duplicate values are notallowed. Unique constraints differ from primary keyconstraints, whose purpose is to identify each table row uniquely, andtypically contain values that have no significance other than being unique.Examples of unique keys include:

       (1)A customer phone number, where the primary key is the customernumber

       (2)A department name, where the primary key is the department number

 

       Asshown in Example2-1, a unique key constraint exists on the email column of the hr.employeestable. The relevant part of the statement is as follows:

CREATE TABLE employees    ( ...

    ,email          VARCHAR2(25)

       CONSTRAINT   emp_email_nn  NOT NULL ...

    ,CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

 

       Theemp_email_uk constraint ensures that no two employees have the same emailaddress, as shown in Example5-1.

 

Example 5-1 Unique Constraint

SQL> SELECT employee_id, last_name,email FROM employees WHERE email = 'PFAY';

 

EMPLOYEE_ID LAST_NAME                 EMAIL

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

       202 Fay                       PFAY

 

SQL> INSERT INTO employees (employee_id,last_name, email, hire_date, job_id)   

 1  VALUES(999,'Fay','PFAY',SYSDATE,'ST_CLERK');

.

.

.

ERROR at line 1:

ORA-00001:unique constraint (HR.EMP_EMAIL_UK) violated

 

       Unless a NOT NULLconstraint is also defined, a null always satisfies a unique key constraint. Thus,columns with both unique key constraints and NOT NULL constraints are typical.This combination forces the user to enter values in the unique key andeliminates the possibility that new row data conflicts with existing row data.

 

Note:

       Because of the searchmechanism for unique key constraints on multiple columns, you cannot haveidentical values in the non-null columns of a partially null composite uniquekey constraint.

 

 

二. 相关测试

2.1 测试unique index 和 uniqueconstraint

SYS@anqing2(rac2)> create table ut(idnumber,phone varchar2(15),name varchar2(15));

Table created.

 

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(1,'13888888888','dave');

1 row created.

SYS@anqing2(rac2)> insert into utvalues(2,'13899999999','dba');

1 row created.

SYS@anqing2(rac2)> commit;

Commit complete.

 

--在phone 字段上,我们创建uniqueconstraint

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

alter table ut add constraint uc_phoneunique(phone)

                              *

ERROR at line 1:

ORA-02299: cannot validate (SYS.UC_PHONE) -duplicate keys found

--这里报错,因为我们在插入数据的时候,有重复值,先删除掉重复值

 

SYS@anqing2(rac2)> select * from ut;

 

       ID PHONE           NAME

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

        1 13888888888     dave

        2 13899999999     dba

        1 13888888888     dave

 

SYS@anqing2(rac2)> delete from ut whererownum=1;

1 row deleted.

 

SYS@anqing2(rac2)> commit;

Commit complete.

 

SYS@anqing2(rac2)> select * from ut;

       ID PHONE           NAME

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

        2 13899999999     dba

        1 13888888888     dave

 

--唯一性约束创建成功

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

--查看约束

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME  INDEX_OWNER  INDEX_NAME

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

UC_PHONE        U UT            SYS           UC_PHONE

--Oracle 自动创建了索引并关联到约束, 索引名和约束名是相同的。

 

--验证下索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

 

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

UC_PHONE      NORMAL        UNIQUE    N

--我们并没有创建索引,而是在创建unique constraint时,oracle 强制创建了uniqueindex。

 

--现在我们drop index 看看

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

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

--这里报错,不能删除unique/primary key 上的索引。在这种情况下,我们只有先删除约束。

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> drop index uc_phone;

drop index uc_phone

          *

ERROR at line 1:

ORA-01418: specified index does not exist

--再次drop 索引时,提示索引已经不存在,说明已经在删除约束的同时,把索引删掉了。

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

结论:

       当约束列上没有索引时,在创建unique constraint 时,oracle 会自动创建unique index,并且该索引不能删除,当删除unique constraint 时,unique index 会自动删除。

 

 

2.2 测试unique constraint 和non-unique index

 

--现在字段phone上创建B-Tree索引

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

 

--查看索引

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--创建unique constraint

SYS@anqing2(rac2)>  alter table ut add constraint uc_phoneunique(phone);

Table altered.

 

--查看约束和索引信息

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

 

CONSTRAINT_NAME C TABLE_NAME   INDEX_OWNER  INDEX_NAME

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

UC_PHONE        U UT            SYS           IDX_UT_PHONE

--这里重用了已经存在的索引

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

--删除索引

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

drop index IDX_UT_PHONE

          *

ERROR at line 1:

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

--这个提示和之前的一样,我们先删除约束,在来查看

 

SYS@anqing2(rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

SYS@anqing2(rac2)> select constraint_name,constraint_type,table_name,index_owner,index_namefrom user_constraints where table_name = 'UT';

no rows selected

--这里约束已经删除掉了。

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

--但是我们的索引并在删除约束时删除掉

 

--在手工删除索引,成功

SYS@anqing2(rac2)> drop indexIDX_UT_PHONE;

Index dropped.

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

 

--重新把约束和索引加上,然后一次删除

SYS@anqing2(rac2)> create indexidx_ut_phone on ut(phone);

Index created.

SYS@anqing2(rac2)> alter table ut addconstraint uc_phone unique(phone);

Table altered.

 

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

CONSTRAINT_NAME C TABLE_NAME    INDEX_OWNER   INDEX_NAME

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

UC_PHONE        U UT            SYS           IDX_UT_PHONE

 

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

INDEX_NAME    INDEX_TYPE    UNIQUENES GENERATED

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

IDX_UT_PHONE  NORMAL       NONUNIQUE N

 

SYS@anqing2(rac2)> alter table ut drop constraint uc_phone drop index;

Table altered.

SYS@anqing2(rac2)> selectconstraint_name,constraint_type,table_name,index_owner,index_name fromuser_constraints where table_name = 'UT';

no rows selected

SYS@anqing2(rac2)> selectindex_name,index_type,uniqueness,generated from user_indexes wheretable_name='UT';

no rows selected

--索引和约束一次删除

 

 

小结:

       当我们的列上有索引时,在创建unique constraint时,Oracle 会重用之前的索引,并且不会改变索引的类型,在第一个测试里,Oracle 自动创建的索引是unique index。

       当我们删除约束时,关联的索引不会自动删除。 这个问题的MOS 上有说明。 参考MOS [ID309821.1]。

       我们可以分两步,先删除约束,在删除索引。 MOS 提供了方法,就是在删除约束时,加上drop index,这样就能一次搞定。

       SQL>altertable ut drop constraint uc_phone drop index;

 

 

 

 

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

QQ:492913789

Email:ahdba@qq.com

Blog: http://www.cndba.cn/dave


DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请


http://www.ngui.cc/el/5183196.html

相关文章

Oracle DSI(Data Server Internals) 下载说明

DSI是Data Server Internals的缩写,是Oracle公司内部用来培训Oracle售后工程师使用的教材. DSI3是Oracle 8系列的, DSI4是Oracle 9系列的. 这样的文档上通常都印着:Oracle Confidential:For internal Use Only. DSI虽然是好教材,但是内容极深,没有深厚的基础是不适合阅读的,尤其…

Oracle 10g Toad查看 表空间 报错 ORA-00600 internal error code arguments [ktfbhget-4], [6], [5]

Toad 10.6 去查Oracle 10g 的表空间,在Toad 返回页面提示信息是: selectprivileges on following are required:DBA_DATA_FILES,DBA_FREE_SPACE,V$TEMP_SPACE_HEADER,v$TEMP_EXTENT_POOL,DBA_TEMP_FILES. 查看了一下DB 的alert log: Fri Jun…

Oracle 10.2.0.4 高负载 触发 ORA-00494 错误

MOS 上有一篇文章专门介绍这个问题,参考:[ID 779552.1]。 在Oracle 10.2.0.4版本中,alert log 可能出现如下错误: ORA-00494:enqueue [CF] held for too long (more than 900 seconds) 这个错误,通常和ORA-600 [2103]…

常用的PL/SQL开发原则 by dbsanke

在微博上看到dbsnake 有一个培训,因为是在北京举办的,过去不。所以问dbsnake 能否把相关的文档传我一份。 dbsnake 邮件给我时说有些东西没有写到文档里。 这个文档也是dbsnake 对Oracle 的一个经验的积累的过程,全部写出来也不实际&#xff…

Oracle 查看 Shared Pool 信息的相关脚本

关于Oracle SGA中Shared Pool的详细说明,参考我的blog: Oracle Shared pool 详解 http://www.cndba.cn/Dave/article/1540 在上篇blog里,介绍了shared pool 的组成和一些原理, 也有一些脚本,在这篇blog里,在…

Oracle Namespace 说明

一. 初识Namespace Oracle通过namespace来管理schema object的名字,关于Namespace 的定义,在官网文档上没有找到一个详细的定义,在网上搜到一些相关信息: SchemaObject Namespaces A namespace defines a group of object types, …

Oracle 游标(cursor) 说明

一. Cursor说明 Oracle里的cursor分为两种:一种是shared cursor,一种是session cursor。 1.1 Shared cursor 说明 sharedcursor就是指缓存在librarycache(SGA下的Shared Pool)里的一种library cache object,说白了就是…

Oracle all_parameters 视图

在dbsnake 的blog里看到一个all_parameters 表,通过这个表可以查看隐含的参数。但是后来确认了下,没有这个表。 所以这个应该是dbsnake 自己建的一个视图。 Oracle 参数分类 和 参数的查看方法 http://www.cndba.cn/Dave/article/1173 在上面的blog里有…

ORA-600 [2662] Block SCN is ahead of Current SCN 处理方法 说明

一. ORA-600[2662] 说明 关于ORA-600[2662]的的错误,之前的blog 有说明: ORA-600[2662] "Block SCN is ahead of Current SCN" [ID 28929.1] http://www.cndba.cn/Dave/article/551 关于ORA-600 的各个参数说明,参考我的blog&…

Oracle datafile block 格式 说明

一. DUMP DatafileBlock 示例 Oracle的一个数据块里的SCN有三种,分别是块头的SCN, CSC (cleanout SCN)和ITL中记录的commit SCN。 如果我们想查看某个或者某些block 里的内容,可以把这些block dump出来查看。 关于这个dump 方法,在…