1 Reply Latest reply: Nov 28, 2012 5:19 PM by Gary Graham-Oracle RSS

    Quick DDL - Can Constraint be generated as alter statement?

    976822
      I'm using 3.2.09. When generating ddl for a table, the PK constraint is included in the create table body. What parameters I need to change to make the PK constraint generated in a seperate ALTER TABLE ADD CONSTRAINT statement? I checked CONSTRAINTS AS ALTERS box under Prefenrences/Database/Utilities/Export but the result is still the same.

      Example:
      CREATE TABLE ABC
      (
      ABC_PK NUMBER(19, 0) NOT NULL
      , ABC_CD NUMBER(5, 0) NOT NULL
      , CONSTRAINT SYS_C0015029 PRIMARY KEY
      (
      ABC_PK
      )
      ENABLE
      ) ;

      Can it be generated like this?
      CREATE TABLE ABC
      (
      ABC_PK NUMBER(19, 0) NOT NULL
      , ABC_CD NUMBER(5, 0) NOT NULL
      ) ;

      ALTER TABLE ABC ADD CONSTRAINT SYS_C0015029 PRIMARY KEY (ABC_PK);
        • 1. Re: Quick DDL - Can Constraint be generated as alter statement?
          Gary Graham-Oracle
          Hi,

          Do not assume Quick DDL (Connections navigator tree > specific connection > Table node > specific table > right-click > Quick DDL) is related to Export. I compared the generated code your ABC table produces on the latest SQL Developer 3.2.20.09.87 in...

          1. The table's object view SQL tab. The PK constraint generates similar to your first case.
          2. Quick DDL to a worksheet. The PK constraints generates similar to your second case. An index is also explicitly generated for the constraint.

          If that's not what you see, then make sure you are using the latest release.
          Case 1:
            CREATE TABLE "SCOTT"."ABC" 
             (     "ABC_PK" NUMBER(19,0) NOT NULL ENABLE, 
               "ABC_CD" NUMBER(5,0) NOT NULL ENABLE, 
                CONSTRAINT "SYS_C0015029" PRIMARY KEY ("ABC_PK")
            USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
            TABLESPACE "USERS"  ENABLE
             ) SEGMENT CREATION DEFERRED 
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
            TABLESPACE "USERS" ;
          
          Case 2:
          --------------------------------------------------------
          --  DDL for Table ABC
          --------------------------------------------------------
          
            CREATE TABLE "SCOTT"."ABC" 
             (     "ABC_PK" NUMBER(19,0), 
               "ABC_CD" NUMBER(5,0)
             ) SEGMENT CREATION DEFERRED 
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
            TABLESPACE "USERS" ;
          --------------------------------------------------------
          --  DDL for Index SYS_C0015029
          --------------------------------------------------------
          
            CREATE UNIQUE INDEX "SCOTT"."SYS_C0015029" ON "SCOTT"."ABC" ("ABC_PK") 
            PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
            TABLESPACE "USERS" ;
          --------------------------------------------------------
          --  Constraints for Table ABC
          --------------------------------------------------------
          
            ALTER TABLE "SCOTT"."ABC" MODIFY ("ABC_PK" NOT NULL ENABLE);
           
            ALTER TABLE "SCOTT"."ABC" MODIFY ("ABC_CD" NOT NULL ENABLE);
           
            ALTER TABLE "SCOTT"."ABC" ADD CONSTRAINT "SYS_C0015029" PRIMARY KEY ("ABC_PK")
            USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
            TABLESPACE "USERS"  ENABLE;
          Regards,
          Gary
          SQL Developer Team