建主键 唯一索引 非唯一索引

SQL> insert into tmp_xf1 values(1);

insert into tmp_xf1 values(1);
insert into tmp_xf1 values(2);

commit;

SQL> alter table tmp_xf1 add constraint pk_tmp_xf1_id primary key(id) using index;
alter table tmp_xf1 add constraint pk_tmp_xf1_id primary key(id) using index
*
ERROR at line 1:
ORA-02437: cannot validate (FBADMIN.PK_TMP_XF1_ID) - primary key violated

---
create index PK_TMP_XF1_ID on tmp_xf1(id);

delete from tmp_xf1 where id =1;

---
SQL> alter table tmp_xf1 add constraint pk_tmp_xf1_id primary key(id) using index;

Table altered.

---

SQL> insert into tmp_xf1 values(2);
insert into tmp_xf1 values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (FBADMIN.PK_TMP_XF1_ID) violated

---

SQL> set autot on
SQL>
SQL> select * from tmp_xf1 where id = 2;

--
INDEX RANGE SCAN| PK_TMP_XF1_ID |
因为索引建的是非唯一索引，所以根据ID查询的时候还是使用INDEX RANGE SCAN

###############

SQL> create unique index PK_TMP_XF1_ID on tmp_xf1(id);

Index created.

SQL> alter table tmp_xf1 add constraint pk_tmp_xf1_id primary key(id) using index;

Table altered.

--then

INDEX UNIQUE SCAN| PK_TMP_XF1_ID |