`
wsql
  • 浏览: 11715501 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

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

 
阅读更多

一. 官网对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_OWNERINDEX_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 NORMALNONUNIQUE 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_OWNERINDEX_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 NORMALNONUNIQUE 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 NORMALNONUNIQUE 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 NORMALNONUNIQUE 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;

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

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

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

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

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

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

分享到:
评论

相关推荐

    Oracle PL/SQL语言初级教程

    唯一性约束(Unique constraint) 51 外部键约束(Foreign key constraint) 53 延迟约束检验(Deferred Constraint Checking) 54 序列(Sequences) 54 索引(INDEXES) 55 6.Oracle数据库数据对象分析(下) 56 过程和...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    在创建表时,经常会创建该表的主键、外键、唯一约束、Check约束等  语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1,column2,…..column_n) ...

    Oracle数据库、SQL

    16.4 unique key:唯一建约束 31 16.5 references foreign key:外键约束 32 16.6 check:检查约束 34 十七、 事务 35 17.1 transaction 35 17.2定义 35 17.3事务的特性:ACID 35 17.4事务的隔离级别 35 17.5数据库...

    最全的oracle常用命令大全.txt

    ORACLE常用命令 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup ...

    Oracle事例

    sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] sql> [logging | nologging] [nosort] ...

    mysql数据库的基本操作语法

    MySQL会给唯一约束的列上默认创建一个唯一索引; create table temp ( id int not null, name varchar(25), password varchar(16), --使用表级约束语法, constraint uk_name_pwd unique(name, password) ); 表示...

    orcale常用命令

    要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>sqlplus SQL>connect internal SQL>startup SQL>quit b、关闭ORACLE系统 oracle>sqlplus SQL>connect internal ...

    2009达内SQL学习笔记

    export ORACLE_HOME=/oracledata/.../bin: 一、注意事项: 大小写不敏感,即不区分大小写。提倡关键字大写,便于阅读和调式。 “!”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成;这些英语单词...

    SQL21日自学通

    在创建索引时使用UNIQUE 关键字214 索引与归并216 群集簇的使用217 总结218 问与答219 校练场219 练习220 第11 天事务处理控制221 目标221 事务控制221 银行应用程序222 开始事务处理223 结束事务处理225 取消...

    精髓Oralcle讲课笔记

    -- 首先,以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba --然后,解除对scott用户的锁 alter user scott account unlock; --那么这个用户名就能使用了。 --(默认全局数据库名orcl) 1、...

    21天学习SQL V1.0

    LPAD 与RPAD................................................................................................. 82 LTRIM 与RTRIM..............................................................................

Global site tag (gtag.js) - Google Analytics