普通索引列和主健列一样导致的问题

SQL> CREATE TABLE EMP AS SELECT * FROM employees;

SQL> desc emp;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

SQL> create index idx_emp_id on emp(employee_id,last_name,email);

SQL> alter table emp add constraint pk_emp_id_name_email primary key(employee_id,last_name,email);

重庆-郑全(24803366) 15:16:10

看到确实只有一个索引:

SQL> select index_name,uniqueness from user_indexes where table_name='EMP';

INDEX_NAME UNIQUENES

------------------------------ ---------

IDX_EMP_ID NONUNIQUE

马上删除索引,报错:

SQL> drop index IDX_EMP_ID;

drop index IDX_EMP_ID

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

索引和主健一样导致的问题

看来主健后建把索引先占住了,先停约束,再删除索引

SQL> alter table emp disable constraint pk_emp_id_name_email;

Table altered.

SQL> drop index IDX_EMP_ID;

Index dropped.

SQL> alter table emp enable constraint pk_emp_id_name_email;

Table altered.

SQL> select index_name,uniqueness from user_indexes where table_name='EMP';

INDEX_NAME UNIQUENES

------------------------------ ---------

PK_EMP_ID_NAME_EMAIL UNIQUE

主健对应的索引又回来了

SQL> create index idx_emp_id on emp(employee_id,last_name,email);

Index created.

SQL> select index_name,uniqueness from user_indexes where table_name='EMP';

INDEX_NAME UNIQUENES

------------------------------ ---------

IDX_EMP_ID NONUNIQUE

PK_EMP_ID_NAME_EMAIL UNIQUE

现在是两个索引了

重庆思庄