Oracle主键、唯一键与唯一索引的区别(三)
ser_indexes
3 where table_owner = 'SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
IDX_TEST_ID NORMAL UNIQUE VALID
实验结果表明，先创建的唯一索引不受约束失效的影响。
SCOTT@ orcl> create index idx_test_id on test(id);
Index created.
SCOTT@ orcl> alter table test add constraint uk_test unique (id);
Table altered.
SCOTT@ orcl> insert into test values(1, 'liu');
insert into test values(1, 'liu')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_TEST) violated
SCOTT@ orcl> alter table test drop constraint uk_test;
Table altered.
SCOTT@ orcl> insert into test values(1, 'liu');
1 row created.
SCOTT@ orcl> delete from test where id=1 and rownum=1;
1 row deleted.
SCOTT@ orcl>
select * from test;
ID NAME
---------- --------------------
yang
1 liu
SCOTT@ orcl> create unique index idx_test_id on test(id);
Index created.
SCOTT@ orcl> alter table test add constraint uk_test unique (id);
Table altered.
SCOTT@ orcl> alter table test drop constraint uk_test;
Table altered.
SCOTT@ orcl> insert into test values(1, 'liu');
insert into test values(1, 'liu')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_TEST_ID) violated
总结如下：
（1）主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除；
（2）主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空；
（3）相同字段序列不允许重复创建索引；