Oracle 索引的定义与约束相同

Oracle index is defined identically to constraint

提问by cdmckay

I am having an issue with Oracle SQL Developer where it keeps telling me my index "Index PK_TBLCASENOTE is defined identically to constraint PK_TBLCASENOTES" whenever I go to the Indexes section of my table TBLCASENOTE.

我在使用 Oracle SQL Developer 时遇到问题，每当我转到表 TBLCASENOTE 的“索引”部分时，它总是告诉我我的索引“索引 PK_TBLCASENOTE 的定义与约束 PK_TBLCASENOTES 相同”。

I found this Oracle form postbut no one seems to have a solution. This is the DDLfor making the table.

我找到了这个Oracle 表单帖子，但似乎没有人有解决方案。 这是制作表格的 DDL。

CREATE TABLE "DSAC"."TBLCASENOTES" (
    "PRIMARYID" NUMBER(11,0), 
    "CLIENTID" NUMBER(11,0), 
    "INTAKEID" NUMBER(11,0), 
    "AGENCYID" NUMBER, 
    "CREATEDDATE" DATE, 
    "CREATEDBY" VARCHAR2(30 BYTE), 
    "MODIFIEDBY" VARCHAR2(30 BYTE), 
    "MODIFIEDDATE" DATE, 
    "TCN" NUMBER DEFAULT 1, 
    "STAFF" VARCHAR2(30 BYTE), 
    "CASENOTETYPE" VARCHAR2(20 BYTE), 
    "CONTACTDATE" DATE, 
    "CONTACTHOUR" VARCHAR2(2 BYTE) DEFAULT null, 
    "CONTACTMIN" VARCHAR2(2 BYTE) DEFAULT null, 
    "CONTACTAMPM" VARCHAR2(2 BYTE) DEFAULT null, 
    "DHOUR" VARCHAR2(2 BYTE) DEFAULT null, 
    "DMIN" VARCHAR2(2 BYTE) DEFAULT null, 
    "BFDATE" DATE, 
    "NOTES" CLOB, 
    CHECK ("PRIMARYID" IS NOT NULL) ENABLE, 
    CHECK ("CLIENTID" IS NOT NULL) ENABLE, 
    CHECK ("INTAKEID" IS NOT NULL) ENABLE, 
    CONSTRAINT "PK_TBLCASENOTES" PRIMARY KEY ("PRIMARYID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "CONTACT"  ENABLE, 
    CONSTRAINT "CASENOTES_CLIENT_FK" FOREIGN KEY ("CLIENTID") REFERENCES "DSAC"."TBLCLIENT" ("CLIENTID") ON DELETE CASCADE ENABLE, 
    CONSTRAINT "CASENOTES_INTAKE_FK" FOREIGN KEY ("INTAKEID") REFERENCES "DSAC"."TBLINTAKE" ("INTAKEID") ON DELETE CASCADE ENABLE
) 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT" 
LOB ("NOTES") STORE AS (
    TABLESPACE "CONTACT" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
    NOCACHE LOGGING 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
);

CREATE INDEX "DSAC"."CASENOTESAGENCYINDEX" ON "DSAC"."TBLCASENOTES" ("AGENCYID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT";

CREATE UNIQUE INDEX "DSAC"."PK_TBLCASENOTE" ON "DSAC"."TBLCASENOTES" ("PRIMARYID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT";

CREATE UNIQUE INDEX "DSAC"."SYS_IL0000059271C00019$$" ON "DSAC"."TBLCASENOTES" (
PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT" 
PARALLEL (DEGREE 0 INSTANCES 0);

CREATE INDEX "DSAC"."TBLCASENOTESCLIENTINDEX" ON "DSAC"."TBLCASENOTES" ("CLIENTID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT";

CREATE INDEX "DSAC"."TBLCASENOTESINTAKEINDEX" ON "DSAC"."TBLCASENOTES" ("INTAKEID") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CONTACT";

回答by OMG Ponies

The issue is because Oracle automatically creates an index for a primary key if no index already exists. The collision is because of attempting to create an index on the same column, though slightly different name.

问题是因为如果不存在索引，Oracle 会自动为主键创建索引。冲突是因为尝试在同一列上创建索引，尽管名称略有不同。

My last place set constraints separately from the table creation, which meant that you'd use the following steps:

我的最后一个地方将约束与表创建分开设置，这意味着您将使用以下步骤：

1. Create table (no constraints, incl. primary key)
2. Create index(es)
3. Apply constraints

1. 创建表（无约束，包括主键）
2. 创建索引
3. 应用约束