主键和唯一约束的索引肯定唯一？

ゝ一世哀愁。 2022-10-09 00:52 275阅读 0赞

这两天在开发过程中，有个需求，就是找出某个schema的所有主键索引和唯一约束索引的名称，逻辑中用到了dba\_indexes，其中存在一个字段叫UNIQUENESS，官方文档解释是说该字段会标记索引是唯一的(UNIQUE)还是非唯一的(NONUNIQUE)，能不能这样理解，对主键索引和唯一约束索引来说，这个字段应该是UNIQUE？

![f019eaf3137f92b7473b03a6d9056406.png][]

如果按常规创建主键约束，自动创建的索引确实这个字段是UNIQUE，

SQL> create table t(id number);
    Table created.
    
    
    SQL> alter table t add constraint pk_t primary key(id);
    Table altered.
    
    
    SQL> select table_name, index_name, uniqueness from dba_indexes
      2  where table_name='T' and owner='BISAL';
    TABLE_NAME                INDEX_NAME                UNIQUENESS
    ------------------------- ------------------------- ---------------
    T                         PK_T                      UNIQUE

如果按常规创建唯一约束，自动创建的索引确实这个字段是UNIQUE，

SQL> create table t(id number);
    Table created.
    
    
    SQL> alter table t add constraint uk_t unique(id);
    Table altered.
    
    
    SQL> select table_name, index_name, uniqueness from dba_indexes
      2  where table_name='T' and owner='BISAL';
    TABLE_NAME                INDEX_NAME                UNIQUENESS
    ------------------------- ------------------------- ---------------
    T                         UK_T                      UNIQUE

但如果我们先创建非唯一索引，然后增加主键，主键就会使用这个索引作为主键索引，但是此时索引的UNIQUENESS字段值就是NONUNIQUE，

SQL> create table t(id number);
    Table created.
    
    
    SQL> create index idx_t on t(id);
    Index created.
    
    
    SQL> alter table t add constraint pk_t primary key(id);
    Table altered.
    
    
    SQL> select table_name, index_name, uniqueness from dba_indexes
      2  where table_name='T' and owner='BISAL';
    TABLE_NAME                INDEX_NAME                UNIQUENESS
    ------------------------- ------------------------- ---------------
    T                         IDX_T                     NONUNIQUE

但这不会影响主键的功能，

SQL> insert into t values(1);
    1 row created.
    
    
    SQL> insert into t values(1);
    insert into t values(1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (BISAL.PK_T) violated

同样地，对唯一约束，如果选择先创建非唯一索引，再增加唯一约束的操作，索引字段的UNIQUENESS就是NONUNIQUE，

SQL> create table t(id number);
    Table created.
    
    
    SQL> alter table t add constraint uk_t unique (id);
    Table altered.
    
    
    SQL> select table_name, index_name, uniqueness from dba_indexes
      2  where table_name='T' and owner='BISAL';
    TABLE_NAME                INDEX_NAME                UNIQUENESS
    ------------------------- ------------------------- ---------------
    T                         IDX_T                     NONUNIQUE

同样不会影响唯一约束的作用，

SQL> insert into t valueS(1);
    1 row created.
    
    
    SQL> insert into t values(1);
    insert into t values(1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (BISAL.UK_T) violated
    
    
    SQL> insert into t values('');
    1 row created.

因此，dba\_indexes的UNIQUENESS字段值是表示索引的唯一性，和约束没有直接的关联。主键约束和唯一约束所对应的索引UNIQUENESS不一定就是UNIQUE，只有当这两种约束都自动创建索引/手工先创建唯一索引的时候，UNIQUENESS的值才是UNIQUE，但是即使是NONUNIQUE，不会影响主键约束和唯一约束的作用。

碰巧前几天，墨天轮每日一题，出了这道题，和上述操作存在异曲同工之处，

![bdfae6d791ff985a8681aaa64e72dab0.png][]

但是这道题不是很严谨，创建成功有前提，就是当前表中不存在重复的数据，如果存在重复的数据，创建会提示错误，如下所示，

SQL> insert into test values(1);
    1 row created.
    
    
    SQL> insert into test values(1); 
    1 row created.
    
    
    SQL> create index idx_test_01 on test(id);
    Index created.
    
    
    SQL> alter table test add constraint pk_test primary key(id);
    alter table test add constraint pk_test primary key(id)
                                    *
    ERROR at line 1:
    ORA-02437: cannot validate (BISAL.PK_TEST) - primary key violated

再扩展下，如果当前表存在重复数据，再创建唯一索引，此时会提示错误，

SQL> create unique index idx_test_01 on test(id);
    create unique index idx_test_01 on test(id)
                                       *
    ERROR at line 1:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

如果创建了唯一索引，再插入重复的值，会提示唯一约束冲突的错误，但此时其实没有任何约束，

SQL> create unique index idx_test_01 on test(id);
    Index created.
    
    
    SQL> insert into test values(1);
    1 row created.
    
    
    SQL> insert into test values(1);
    insert into test values(1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (BISAL.IDX_TEST_01) violated
    
    
    SQL> select constraint_name, index_name from 
         user_constraints where table_name='TEST';
    no rows selected

说明如果仅存在唯一索引的时候，他会保证数据的唯一性。如果存在主键或者唯一约束，即使索引不唯一，还是能限制数据的重复性。

程序猿劳逸结合，看会儿球，

《[欧洲杯第二周的比赛闲聊][Link 1]》

《[欧洲杯第一周的比赛闲聊][Link 2]》

**近期更新的文章：**

《[SpringBoot开发过程中碰到的几个问题][SpringBoot]》

《[见识一下SQL Server隐式转换处理的不同][SQL Server]》

《[小白学习MySQL - 查询会锁表？][MySQL -]》

《[误操作怎么办？试试这个神器-Log Miner][-Log Miner]》

《[数据库hang等待链分析利器][hang]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[f019eaf3137f92b7473b03a6d9056406.png]: /images/20221005/c2a00f47b2ed4cb28772699e438beb22.png
[bdfae6d791ff985a8681aaa64e72dab0.png]: /images/20221005/bf08e2d017ef4bcabc10178e3adec1dc.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a6faddf52fecf0f1d353fbd8d7bf7682991d52c3d6a925ec5b6b7c8012bf6dc8c5ddd8ac&idx=1&mid=2247492361&scene=21&sn=8ef8df780b285fbf69805fbdadabc850#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a721ddf52e37ab0b5c8109f2884c6cc8905ae5a94a5b1180f955d3d378456e484a1077c3&idx=1&mid=2247492306&scene=21&sn=b850cb9add43e39ce94c69501ae50c72#wechat_redirect
[SpringBoot]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a71addf52e0c0d9d9f2b7645e81a7a26f659efbdac3448055005b897b327b632d486621c&idx=1&mid=2247492329&scene=21&sn=ef2b42e9ac9e16288f7488a54e305cf0#wechat_redirect
[SQL Server]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a72cddf52e3ae3cc45ba92dd5083dd963358b1f07b7dc98348683252d4f4bc66c397ed1a&idx=1&mid=2247492319&scene=21&sn=0bf77c5d4db98818038f906fbe11a809#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a751ddf52e47df120a4c3f4cd841cd06383a3cc406398d6feb9185e17d1167c53e1c0a56&idx=1&mid=2247492258&scene=21&sn=d1859e3c1e3f2bee0e2c7801c8dc0b5b#wechat_redirect
[-Log Miner]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a769ddf52e7fe1c62e13d67af71df7907560c9dcb8f885fc2986afda123202a5ed740254&idx=1&mid=2247492250&scene=21&sn=2ed8a58ec992247a2412411229ccbc06#wechat_redirect
[hang]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7a4ddf52eb2590eacab606223bb71dd660be9955ad297a5e44b9314c60266e13c962f34&idx=1&mid=2247492183&scene=21&sn=a80b4e1d743da42ca26aff7d8d022238#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect