Oracle index is defined identically to the constraint
I have a problem with Oracle SQL Developer where it keeps telling me my index "Index PK_TBLCASENOTE is defined identically to PK_TBLCASENOTES constraint" whenever I go to the Indexes section of my TBLCASENOTE table.
I found this post in Oracle form , but no one seems to have a solution. This is the DDL for table creation.
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";
+2
a source to share
1 answer
The problem is that Oracle automatically creates an index on the primary key if the index does not already exist. The collision is due to an attempt to create an index on the same column, although a slightly different name.
My last place was setting constraints separate from table creation, which meant you would use the following steps:
- Create table (no limitation, including primary key)
- Create index (s)
- Apply restrictions
+4
a source to share