Advertisement
Guest User

Oracle Index/Constraint Issue DDL

a guest
Apr 14th, 2010
287
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.35 KB | None | 0 0
  1. CREATE TABLE "DSAC"."TBLCASENOTES"
  2.    (    "PRIMARYID" NUMBER(11,0),
  3.     "CLIENTID" NUMBER(11,0),
  4.     "INTAKEID" NUMBER(11,0),
  5.     "AGENCYID" NUMBER,
  6.     "CREATEDDATE" DATE,
  7.     "CREATEDBY" VARCHAR2(30 BYTE),
  8.     "MODIFIEDBY" VARCHAR2(30 BYTE),
  9.     "MODIFIEDDATE" DATE,
  10.     "TCN" NUMBER DEFAULT 1,
  11.     "STAFF" VARCHAR2(30 BYTE),
  12.     "CASENOTETYPE" VARCHAR2(20 BYTE),
  13.     "CONTACTDATE" DATE,
  14.     "CONTACTHOUR" VARCHAR2(2 BYTE) DEFAULT NULL,
  15.     "CONTACTMIN" VARCHAR2(2 BYTE) DEFAULT NULL,
  16.     "CONTACTAMPM" VARCHAR2(2 BYTE) DEFAULT NULL,
  17.     "DHOUR" VARCHAR2(2 BYTE) DEFAULT NULL,
  18.     "DMIN" VARCHAR2(2 BYTE) DEFAULT NULL,
  19.     "BFDATE" DATE,
  20.     "NOTES" CLOB,
  21.      CHECK ("PRIMARYID" IS NOT NULL) ENABLE,
  22.      CHECK ("CLIENTID" IS NOT NULL) ENABLE,
  23.      CHECK ("INTAKEID" IS NOT NULL) ENABLE,
  24.      CONSTRAINT "PK_TBLCASENOTES" PRIMARY KEY ("PRIMARYID")
  25.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  26.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  27.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  28.   TABLESPACE "CONTACT"  ENABLE,
  29.      CONSTRAINT "CASENOTES_CLIENT_FK" FOREIGN KEY ("CLIENTID")
  30.       REFERENCES "DSAC"."TBLCLIENT" ("CLIENTID") ON DELETE CASCADE ENABLE,
  31.      CONSTRAINT "CASENOTES_INTAKE_FK" FOREIGN KEY ("INTAKEID")
  32.       REFERENCES "DSAC"."TBLINTAKE" ("INTAKEID") ON DELETE CASCADE ENABLE
  33.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  34.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  35.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  36.   TABLESPACE "CONTACT"
  37.  LOB ("NOTES") STORE AS (
  38.   TABLESPACE "CONTACT" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  39.   NOCACHE LOGGING
  40.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  41.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
  42.  
  43.  
  44.   CREATE INDEX "DSAC"."CASENOTESAGENCYINDEX" ON "DSAC"."TBLCASENOTES" ("AGENCYID")
  45.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  46.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  47.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  48.   TABLESPACE "CONTACT" ;
  49.  
  50.   CREATE UNIQUE INDEX "DSAC"."PK_TBLCASENOTE" ON "DSAC"."TBLCASENOTES" ("PRIMARYID")
  51.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  52.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  53.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  54.   TABLESPACE "CONTACT" ;
  55.  
  56.   CREATE UNIQUE INDEX "DSAC"."SYS_IL0000059271C00019$$" ON "DSAC"."TBLCASENOTES" (
  57.   PCTFREE 10 INITRANS 2 MAXTRANS 255
  58.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  59.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  60.   TABLESPACE "CONTACT"
  61.   PARALLEL (DEGREE 0 INSTANCES 0) ;
  62.  
  63.   CREATE INDEX "DSAC"."TBLCASENOTESCLIENTINDEX" ON "DSAC"."TBLCASENOTES" ("CLIENTID")
  64.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  65.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  66.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  67.   TABLESPACE "CONTACT" ;
  68.  
  69.   CREATE INDEX "DSAC"."TBLCASENOTESINTAKEINDEX" ON "DSAC"."TBLCASENOTES" ("INTAKEID")
  70.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  71.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  72.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  73.   TABLESPACE "CONTACT" ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement