Why dropping and creating a PK in oracle12c makes the PK disappear from `all_indexes`?

Question

I'm trying to reset a primary key "hail mary" style - drop and create.

But, afterwards, it disappears from the all_indexes table.

The PK appears to be there because the behavior is the correct one:

1. When trying to create it again, it says I can only create a single primary key.
2. Also, cannot insert 2 rows with same ids - unique constraint violation.

Script:

set echo on;
-- whenever sqlerror exit sql.sqlcode;
set timing on
SET LINESIZE 2000

create table MY_TABLE (
    date_col DATE DEFAULT SYSTIMESTAMP NOT NULL,
    id NUMBER(10) NOT NULL,
    hash NUMBER(19) DEFAULT '0' NOT NULL,
    value1 NUMBER(19) DEFAULT '0' NOT NULL,
    value2 NUMBER(19) DEFAULT '0' NOT NULL,
    CONSTRAINT MY_TABLE_PK PRIMARY KEY (date_col, id, hash)
);
-- Res: Table created.

CREATE INDEX MY_TABLE_I ON MY_TABLE (id, hash, date_col ASC);
-- Res: Index created.

select index_name from all_indexes where table_name = 'MY_TABLE' order by index_name;
-- Res: MY_TABLE_I, MY_TABLE_PK

ALTER TABLE MY_TABLE DROP primary key;
-- Res: Table altered.
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_PK PRIMARY KEY (date_col, id, hash);
-- Res: Table altered.
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_PK PRIMARY KEY (date_col, id, hash);
-- Res: ORA-02260: table can have only one primary key

insert into MY_TABLE values (SYSDATE, 1, 123456, 10, 20);
-- Res: 1 row created.
insert into MY_TABLE values (SYSDATE, 1, 123456, 10, 20);
-- Res: ORA-00001: unique constraint (ALIK.MY_TABLE_PK) violated

select index_name from all_indexes where table_name = 'MY_TABLE' order by index_name;
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- Res: !!!!! Just MY_TABLE_I !!!!!
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Any idea why the PK disappears from all_indexes after PK recreation?

DB version: 12.1.0.2.0

Cheers.

Answer 1

When you add a primary key or unique constraint, the database checks if there's an existing index with the same leading columns. If there is, the database can use that.

The database will do this even if the index columns are in a different order to the constraint. So as MY_TABLE_I has the same columns as the PK, the database uses that.

If you look in *_constraints, you should find that the PK uses the index MY_TABLE_I:

select constraint_name, index_name
from   user_constraints
where  table_name = 'MY_TABLE' 
and    constraint_type = 'P';

CONSTRAINT_NAME    INDEX_NAME   
MY_TABLE_PK        MY_TABLE_I  

To avoid this, use the using index clause to define/specify the index you want the PK to use:

ALTER TABLE MY_TABLE 
  ADD CONSTRAINT MY_TABLE_PK 
  PRIMARY KEY (date_col, id, hash)
  USING INDEX ( 
    CREATE INDEX MY_TABLE_PK ON MY_TABLE (date_col, id, hash) 
  );