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.
I found this Oracle form post but no one seems to have a solution. This is the DDL for making the table.
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 "CASE开发者_开发百科NOTES_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";
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.
My last place set constraints separately from the table creation, which meant that you'd use the following steps:
- Create table (no constraints, incl. primary key)
- Create index(es)
- Apply constraints
精彩评论