代码之家  ›  专栏  ›  技术社区  ›  Raj More

在Oracle SQL中创建默认约束

  •  0
  • Raj More  · 技术社区  · 14 年前

    我从SQL Server学习了一些Oracle语法。

    这是我的桌子DDL

    CREATE TABLE "CDR"."EXTDL_REPORTSETS"
      (
        "ID"                NUMBER(38,0) NOT NULL ENABLE,
        "SHORTNAME"         NUMBER(38,0) NOT NULL ENABLE,
        "DESCRIPTION"       NUMBER(38,0) NOT NULL ENABLE,
        "ASOFSTARTDATETIME" NUMBER(38,0) NOT NULL ENABLE,
        "ASOFENDDATETIME"   NUMBER(38,0) NOT NULL ENABLE,
        CONSTRAINT "PK_EXTDL_REPORTSETS" PRIMARY KEY ("ID") USING INDEX 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 "CDR_DATA" 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 "CDR_DATA" ;
    

    我想将asofstartdatetime默认为sysdate。如何在pl/sql中设置默认约束?

    编辑

    数据类型弄乱了,所以我更正了它。

    CREATE TABLE CDR.ExtDL_Reportsets(
        Id                   NUMBER(38, 0)    NOT NULL,
        ShortName            VARCHAR2(255)    NOT NULL,
        Description          VARCHAR2(500)    NOT NULL,
        AsOfStartDateTime    DATE             NOT NULL,
        AsOfEndDateTime      DATE,
        CONSTRAINT PK_ExtDL_Reportsets PRIMARY KEY (Id)
    )
    ;
    
    1 回复  |  直到 14 年前
        1
  •  4
  •   OMG Ponies    14 年前

    用途:

    ALTER TABLE EXTDL_REPORTSETS
         MODIFY last_ASOFSTARTDATETIME DATE DEFAULT SYSDATE
    

    我找不到10g reference documentation - this is for 11g .

    如果要在create table语句中执行此操作:

    CREATE TABLE "CDR"."EXTDL_REPORTSETS" (
      "ID"                NUMBER(38,0) NOT NULL ENABLE,
      "SHORTNAME"         VARCHAR2(255) NOT NULL ENABLE,
      "DESCRIPTION"       VARCHAR2(500) NOT NULL ENABLE,
      "ASOFSTARTDATETIME" DATE DEFAULT SYSDATE NOT NULL ENABLE,
      "ASOFENDDATETIME"   DATE NOT NULL ENABLE,
      CONSTRAINT "PK_EXTDL_REPORTSETS" PRIMARY KEY ("ID") USING INDEX 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 "CDR_DATA" 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 "CDR_DATA" ;