Почему пересоздание PK (первичного ключа) не приводит к созданию нового индекса?

Question

Пытаюсь пересоздать первичный ключ, то есть - drop и create. После этого, новый индекс для первичного ключа не создаётся. Хотя сушествует другой индекс, но он же не уникальный и имеет совсем другой порядок колонок.

PK в таблице присутствует, потому что поведение правильное:

При попытке создать его снова, ошибка - можно создать только один первичный ключ.
Также при вставке 2-х одинаковых записей, ошибка - нарушена уникальность.

Вот так создаётся таблица:

create table tab (
    datecol DATE DEFAULT SYSDATE NOT NULL,
    id NUMBER(10) NOT NULL,
    hash NUMBER(19) DEFAULT 0 NOT NULL,
    col NUMBER(19) DEFAULT 0 NOT NULL,
    CONSTRAINT tab_pk PRIMARY KEY (datecol, id, hash));

Table TAB created.

create index tab_ix ON tab (id, hash, datecol ASC);

Index TAB_IX created.

select index_name from user_indexes where table_name = 'TAB';

INDEX_NAME
----------------
TAB_PK
TAB_IX

Так пересоздаю первичный ключ и проверяю, что он работает:

alter table tab DROP primary key;

Table TAB altered.

alter table tab ADD CONSTRAINT tab_pk PRIMARY KEY (datecol, id, hash);

Table TAB altered.

alter table tab ADD CONSTRAINT tab_pk PRIMARY KEY (datecol, id, hash);
ORA-02260: table can have only one primary key

insert into TAB values (date'2020-01-01', 1, 123456, 10, 20);

1 row inserted.

insert into tab values (date'2020-01-01', 1, 123456, 10, 20);
ORA-00001: unique constraint (TAB_PK) violated

select index_name from user_indexes where table_name = 'TAB';

INDEX_NAME
----------------
TAB_IX

Почему пересоздание PK не приводит к созданию нового индекса?

_{Свободный перевод вопроса Why dropping and creating a PK makes the PK disappear from all_indexes? от участника @Alikelzin-kilaka}

Answer 1

При создании первичного ключа или уникального ограничения БД проверяет, существует ли уже индекс с теми же столбцами. Если такой уже имеется, то БД будет использовать его для обеспечения ограничения, а не создавать новый.

БД поступит так, даже если индекс не уникальный и если его столбцы расположены в другом порядке, чем в ограничении. Решающее здесь - сможет ли существующий индекс обеспечить ограничение.

Если посмотреть в словаре данных для ограничений, то видно, что PK использует существующий индекс:

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

CONSTRAINT_NAME  INDEX_NAME      
---------------- ----------------
TAB_PK           TAB_IX

Чтобы избежать этого, надо использовать клаузу using index для определения/указания индекса, который вы должны использовать в PK:

ALTER TABLE tab ADD CONSTRAINT tab_pk PRIMARY KEY (datecol, id, hash)
USING INDEX ( 
    CREATE INDEX tab_pk ON tab (datecol, id, hash));

_{Свободный перевод ответа от участника @Chris Saxon}

БЛОГ НА HUSL

Почему пересоздание PK (первичного ключа) не приводит к созданию нового индекса?

Ответы (1 шт):