时间戳

首页 > 解决方案 > 为什么在 oracle12c 中删除和创建 PK 会使 PK 从 `all_indexes` 中消失？

oracle - 为什么在 oracle12c 中删除和创建 PK 会使 PK 从 `all_indexes` 中消失？

问题描述

我正在尝试重置主键“hail mary”样式 - 删除并创建。

但是，之后，它从桌子上消失了all_indexes。

PK 似乎在那里，因为行为是正确的：

  1. 当尝试再次创建它时，它说我只能创建一个主键。
  2. 此外，不能插入具有相同 id 的 2 行 - 违反唯一约束。

脚本：

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 !!!!!
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

知道为什么all_indexesPK 游戏后 PK 会消失吗？

数据库版本：12.1.0.2.0

干杯。

标签： oracleindexingprimary-keyoracle12c

解决方案

添加主键或唯一约束时，数据库会检查是否存在具有相同前导列的现有索引。如果有，数据库可以使用它。

即使索引列与约束的顺序不同，数据库也会这样做。因此，MY_TABLE_I与 PK 具有相同的列，数据库使用它。

如果您查看*_constraints，您应该会发现 PK 使用索引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

为避免这种情况，请使用该using index子句定义/指定您希望 PK 使用的索引：

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) 
  );

推荐阅读