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

Oracle中主键、唯一约束与唯一索引之区别 概述一般在Oracle数据表中，我们都会看到主键，索引，也会看到唯一索引、唯一约束，那么他们有什么区别呢，下面通过一个小实验简单了解一下。 实验过程：首先创建一个表，并完成主键、唯一约束、唯一索引的创建，如下所示： SQL--数据库版本SQL select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 – Production SQL conn firsoul/firsoul Connected. 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) violated SQL 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) violated SQL 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 150 SQL col table_name for a15 SQL col column_name for a15 SQL col constraint_name for a20 SQL select table_name,column_name,constraint_name from user_cons_columns where table_name=upper(test); TABLE_NA