Want more features on Pastebin? Sign Up, it's FREE!
Guest

Oracle Index/Constraint Issue DDL

By: a guest on Apr 14th, 2010  |  syntax: SQL  |  size: 3.35 KB  |  views: 106  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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" ;
clone this paste RAW Paste Data