【INDEX】Oracle中主键、唯一约束与唯一索引之区别.docx

Oracle中主键、唯一约束与唯一索引之区别一、 概述一般在Oracle数据表中，我们都会看到主键，索引，也会看到唯一索引、唯一约束，那么他们有什么区别呢，下面通过一个小实验简单了解一下。二、 实验过程：首先创建一个表，并完成主键、唯一约束、唯一索引的创建，如下所示：SQL-数据库版本SQL select * from v$version;BANNER-Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 ProductionSQL conn firsoul/firsoulConnected.SQL -创建测试表SQL create table test (tno number(5),tname varchar2(20),tid int);Table created.SQL-添加主键SQL alter table test add constraint pk_test_tno primary key(tno);Table altered.SQL-添加唯一约束SQL alter table test add constraint uk_test_tname unique(tname);Table altered.SQL-添加唯一索引SQL create unique index idx_test_tid on test(tid);Index created.向表TEST中插入数据，测试SQL insert into test values(1,firsoul,123);1 row created.SQL 1 1* insert into test values(1,firsoul,123)SQL /insert into test values(1,firsoul,123)*ERROR at line 1:ORA-00001: unique constraint (FIRSOUL.PK_TEST_TNO) violatedSQL insert into test values(2,firsoul,123456);insert into test values(2,firsoul,123456)*ERROR at line 1:ORA-00001: unique constraint (FIRSOUL.UK_TEST_TNAME) violatedSQL insert into test values(2,ljx,123);insert into test values(2,ljx,123)*ERROR at line 1:ORA-00001: unique constraint (FIRSOUL.IDX_TEST_TID) violatedSQL insert into test values(null,ljx,124);insert into test values(null,ljx,124) *ERROR at line 1:ORA-01400: cannot insert NULL into (FIRSOUL.TEST.TNO)SQL insert into test values(2,null,null);1 row created.SQL commit;Commit complete. 有以上信息，我们可以看出主键列的值不允许为NULL，也不允许出现重复值，唯一约束、唯一索引列的值允许为NULL，但不允许出现重复值。 查看字典表中约束的情况SQL set lines 150SQL col table_name for a15SQL col column_name for a15SQL col constraint_name for a20SQL select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME CONSTRAINT_NAME- - -TEST TNO PK_TEST_TNOTEST TNAME UK_TEST_TNAME查看字典表中索引的情况SQL select table_name,column_name,index_name from user_ind_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME INDEX_NAME- - -TEST TNO PK_TEST_TNOTEST TNAME UK_TEST_TNAMETEST TID IDX_TEST_TIDSQL select index_name,uniqueness,tablespace_name from user_indexes where table_name=upper(test);INDEX_NAME UNIQUENES TABLESPACE_NAME- - -IDX_TEST_TID UNIQUE FIRSOULUK_TEST_TNAME UNIQUE FIRSOULPK_TEST_TNO UNIQUE FIRSOUL在唯一索引上创建唯一约束，TID列增加了约束，并没有继续增加唯一索引。SQL alter table test add constraint uk_test_tid unique(tid);Table altered.SQL set lines 150SQL col table_name for a15SQL col column_name for a15SQL col constraint_name for a20SQL select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME CONSTRAINT_NAME- - -TEST TNO PK_TEST_TNOTEST TNAME UK_TEST_TNAMETEST TID UK_TEST_TIDSQL select table_name,column_name,index_name from user_ind_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME INDEX_NAME- - -TEST TNO PK_TEST_TNOTEST TNAME UK_TEST_TNAMETEST TID IDX_TEST_TID通过上边我们可以看出，创建主键、唯一约束时，会同时创建与约束一致名字的索引。下面我们删除唯一约束，查看效果SQL alter table test drop constraint UK_TEST_TNAME;Table altered.SQL alter table test drop constraint UK_TEST_TID;Table altered.SQL select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME CONSTRAINT_NAME- - -TEST TNO PK_TEST_TNOSQL select table_name,column_name,index_name from user_ind_columns where table_name=upper(test);TABLE_NAME COLUMN_NAME INDEX_NAME- - -TEST TNO PK_TEST_TNOTEST TID IDX_TEST_TID 通过上面结果我们可以得知，手