7 Replies Latest reply: May 18, 2011 2:34 AM by user645399 RSS

    how to change not null column to null  ?

    NicloeiW
      hi,
      is there any way to change not null column to null ?

      Create Table test
      (a Number(10) Not Null,
      b Date
      );

      i need column a to be nullable, can this be done ?

      i am using direct load of data from .dat file
      in which first column i.e. a is null,
      hence i need to modify the table column
      definition so that column a is nullable.

      plz assist

      regards
      nic
      Message was edited by:
      Nicloei W
        • 1. Re: how to change not null column to null  ?
          566473
          SQL> create table test (a number not null, b date);
          Table created.
          SQL> desc test;
          Name                                      Null?    Type
          ----------------------------------------- -------- ----------------------------
          A                                         NOT NULL NUMBER
          B                                                  DATE
          SQL> insert into test values(null, sysdate);
          insert into test values(null, sysdate)
          *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."A")
          SQL> alter table test modify(a null);
          Table altered.
          SQL> desc test;
          Name                                      Null?    Type
          ----------------------------------------- -------- ----------------------------
          A                                                  NUMBER
          B                                                  DATE
          SQL> insert into test values(null, sysdate);
          1 row created.
          SQL> select * from test;
                   A B                                                                    
          ---------- --------                                                            
                     18.07.08                                                
          • 2. Re: how to change not null column to null  ?
            NicloeiW
            many thanks, yes the issue is solved,
            i was doing the following thing,
            alter table test modify (a number default null);

            but this doesnt work;

            thanks once again
            regards
            nic
            • 3. Re: how to change not null column to null  ?
              JackK
              Your sollution didn't work for me.
              After
              alter table G5ADR_G5BUD_RADR modify (ID_G5BUD null);
              the NOT NULL check constraint (SEARCH_CONDITION: "ID_G5BUD" IS NOT NULL) disapears but the column is still marked as NULLABLE=No.
              What's wrong?
              • 4. Re: how to change not null column to null  ?
                Saubhik
                Please do not dig out 2 years past posts.
                • 5. Re: how to change not null column to null  ?
                  JackK
                  Isn't it better to dig old posts than writing another topic when problem is exactly the same?
                  • 6. Re: how to change not null column to null  ?
                    JackK
                    Sorry. I am seeing now that the column is part of a 2-column PK.
                    • 7. Re: how to change not null column to null  ?
                      user645399
                      Hi Sergey,

                      I was looking for a way to make my column nullable and your answer in this thread helped me. Thanks!

                      Cheers!

                      Nith