5 Replies Latest reply on Apr 18, 2014 2:14 AM by rp0428

    Column to be modified to NOT NULL is already NOT NULL

    Davide Lenza
    Davide Lenza Apr 17, 2014 11:11 AM

      Good Morning,

      I'm using SQL Developer to manage my DB and I have some problems with the export function because it produces a script separating NOT NULL constraints from CREATE instructions.

      The NOT NULL constraints are specified inside ALTER TABLE instructions.

       

      For example:

       

      CREATE TABLE "BASIC_APP_APPLICATION"

         (

        "ID" NUMBER(13,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE ,

        "CODE" CHAR(10 BYTE)

         )

        SEGMENT CREATION IMMEDIATE

        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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "SYSTEM" ;

       

        ALTER TABLE "DBDEVELOPER"."BASIC_APP_APPLICATION" MODIFY ("ID" NOT NULL ENABLE);

        ALTER TABLE "DBDEVELOPER"."BASIC_APP_APPLICATION" MODIFY ("CODE" NOT NULL ENABLE);

        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "SYSTEM"  ENABLE;

       

      This behaviour causes the "column to be modified to NOT NULL is already NOT NULL" error when the red instruction is performed.

      How could I solve this problem?

      Is it possibile with SQL Developer to obtain a script SQL like the following?

      CREATE TABLE BASIC_APP_APPLICATION

      (

        ID NUMERIC(13, 0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE NOT NULL,

        CODE CHAR(10) NOT NULL

      );


      Thanks a lot.

      Bye

      Davide


      • 3081 Views
      • Tags:
        • 1. Re: Column to be modified to NOT NULL is already NOT NULL
          Davide Lenza
          Davide Lenza Apr 17, 2014 1:07 PM (in response to Davide Lenza)

          Can anyone help me please?

          Thank you

          • 2. Re: Column to be modified to NOT NULL is already NOT NULL
            Gary Graham-Oracle
            Gary Graham-Oracle Apr 17, 2014 9:14 PM (in response to Davide Lenza)

            Hi Davide,

             

            Which version of the SQL Developer tool are you using?  It is true non-null constraints in the export are expressed as distinct ALTER statements after the table CREATE statement, but using 4.0.1.14.48 I tried the following...

            1. Cut/paste your DDL directly into a worksheet and compile it.  It errors on "SQL Error: ORA-02000: missing ALWAYS keyword..."
            2. As a workaround, define the equivalent objects using the New Table > Advanced object editor.
            3. Export them with the Dependency, Drop, and Drop Cascade options (i.e., the table plus the associated sequence and trigger)
            4. Then import the exported DDL. This works without fatal errors (But sequence associated with Identity column is not dropped, so its CREATE fails).

             

            So on the latest production 4.0.1 version, I do not see how you get

            "column to be modified to NOT NULL is already NOT NULL"

            Can you clarify that?

             

            Regards,

            Gary

            SQL Developer Team

            • 3. Re: Column to be modified to NOT NULL is already NOT NULL
              rp0428 Apr 18, 2014 12:14 AM (in response to Gary Graham-Oracle)
              • Cut/paste your DDL directly into a worksheet and compile it.  It errors on "SQL Error: ORA-02000: missing ALWAYS keyword..."

              Hmmm - can you clarify that? I don't get any exception creating the table using 4.0.1.14.48 on Win XP SP3.

               

              1. you 'compile' code but not DDL. How did you 'compile' a create table statement? I can 'run' the statement or 'run' the script but now sure about 'compile'.

               

              2. Which DDL in particular do you mean? If I paste the CREATE TABLE ddl after removing the tablespace clause (you should NEVER create objects in the SYSTEM tablespace) it creates the table just fine

               

              3. You shouldn't get a 'missing ALWAYS keyword' exception at all since the DDL uses 'GENERATED BY DEFAULT', The two options are mutually exclusive. See the 'identity_clause' syntax diagram in the SQL Language doc:

              http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm

              identity_clause

               

              Use this clause to specify an identity column. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator.

              ALWAYS If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.

              BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

              I'm puzzled by just what you did for that first step.

              So on the latest production 4.0.1 version, I do not see how you get

              "column to be modified to NOT NULL is already NOT NULL"

              Can you clarify that?

              The IDENTITY column creates the column as NOT NULL. So you cannot alter the table to use NOT NULL because it already is.

              • 4. Re: Column to be modified to NOT NULL is already NOT NULL
                Gary Graham-Oracle
                Gary Graham-Oracle Apr 18, 2014 7:29 AM (in response to rp0428)

                Haa!  Thanks for setting me straight. I obviously should have skipped this discussion, not really having had time today to think it through. Among my mistakes...

                1. you 'compile' code but not DDL

                Gross misuse of the word "compile".  I guess code either gets interpreted or compiled, then executed, a.k.a  "run".

                2. Which DDL in particular do you mean?

                The entire CREATE TABLE statement, as-is, except with blank lines removed...  but I tested it against an Oracle release that does not support it...

                3. You shouldn't get a 'missing ALWAYS keyword' exception at all

                Except that is what the 4.0.1 worksheet spit back at me when I ran 12c syntax against an 11.2.0.1 DB.  The alternative on 11.2 is to simulate the Identity functionality via a combination of sequences and triggers.

                The IDENTITY column creates the column as NOT NULL. So you cannot alter the table to use NOT NULL because it already is.

                So that must be the bug the OP is seeing.  I will log a bug for it if necessary.  Thanks for your comments.

                 

                P.S.: Logged the following internal / unpublished bug for this: EXPORT DDL WITH IDENTITY COLUMN HAS REDUNDANT ALTER NOT NULL


                 

                Message was edited by: Gary Graham

                • 5. Re: Column to be modified to NOT NULL is already NOT NULL
                  rp0428 Apr 18, 2014 2:14 AM (in response to Gary Graham-Oracle)

                  Well OP never really explicity said they were using 12c.

                   

                  But they said they were exporting an existing table and had this in the DDL:

                  GENERATED BY DEFAULT AS IDENTITY

                  The combination of the two told me they could ONLY be on 12c or they could not have created the table in the first place.

                   

                  Frankly, I'm surprised there haven't been more issues reported now that people are starting to experiment with 12c. The only other one was about the 'export data' as INSERT includes the IDENTITY column and its values and that doesn't work for the GENERATED ALWAYS (the default) since the value will be, well, always generated.

                   

                  They have a workaround for that by manually excluding the identity column from the data export. Except the user doing the exporting may not know that there even is an identity column or how it is defined.