10 Replies Latest reply on Jul 27, 2018 1:13 PM by jkoradba

    Cart Export utility - redundant statement generated for PK identity column NOT NULL

    jkoradba

      I am using the Export utility from the Cart to generate the ddl script for a number of tables, and getting errors in the Constraints section when I execute the script. The primary keys column uses and identity sequence. In the generated script I also get an alter statement that I think shouldn’t be there (alter … column … not null). If I uncheck Constraints then I will not get the alter table … primary key statement. Is there a way around this issue or is this a bug?

       

      --------------------------------------------------------

      --  DDL for Table MY_TABLE

      --------------------------------------------------------

       

       

        CREATE TABLE "MY_TABLE"

         ( "COLUMN_1" VARCHAR2(255 CHAR),

      "MY_TABLE_SID" NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 11 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE

         ) SEGMENT CREATION DEFERRED

        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

      NOCOMPRESS LOGGING

        TABLESPACE "USERS"   NO INMEMORY ;

      REM INSERTING into MY_TABLE

      SET DEFINE OFF;

      --------------------------------------------------------

      --  DDL for Index MY_TABLE_PK

      --------------------------------------------------------

       

       

        CREATE UNIQUE INDEX "MY_TABLE_PK" ON "MY_TABLE" ("MY_TABLE_SID")

        PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

        TABLESPACE "USERS" ;

      --------------------------------------------------------

      --  Constraints for Table MY_TABLE

      --------------------------------------------------------

       

       

        ALTER TABLE "MY_TABLE" MODIFY ("MY_TABLE_SID" NOT NULL ENABLE);

        ALTER TABLE "MY_TABLE" ADD CONSTRAINT "MY_TABLE_PK" PRIMARY KEY ("MY_TABLE_SID")

        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

        TABLESPACE "USERS"  ENABLE;

       

       

       

      From ddl script execution:

       

       

      ——————————————————–
      — Constraints for Table MY_TABLE
      ——————————————————–

      ALTER TABLE “MY_TABLE” MODIFY (“MY_TABLE_SID” NOT NULL ENABLE);
      ALTER TABLE “MY_TABLE” ADD CONSTRAINT “MY_TABLE_SPK” PRIMARY KEY (“MY_TABLE_SID”)
      USING INDEX ENABLE;

      Error starting at line : 996 in command –
      ALTER TABLE “MY_TABLE” MODIFY (“MY_TABLE_SID” NOT NULL ENABLE)
      Error report –
      ORA-01442: column to be modified to NOT NULL is already NOT NULL
      01442. 00000 – “column to be modified to NOT NULL is already NOT NULL”
      *Cause:
      *Action:

      Table “MY_TABLE” altered. —-> this is for the pk constraint