DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Post Question: In Oracle, is there a way to remove the default of the column of a table ?


    In Oracle, I created a table with nn columns.
    In one of the columns I defined a default value.
    Now the default value is not valid anymore.
    I would like to keep the column but remove the default.

    Is there a way to remove the default of the column of a table ?

    The following is an example of my question:

    create table test (
    t1 number,
    t2 number default 0
    ) ;

    insert into test(t1,t2) values (1,1);
    insert into test(t1) values (2);

    -- The following does not work to remove the default
    alter table test modify (
    t2 number) ;

    insert into test(t1) values (3);

    Any ideas besides dropping and recreating the column ?

  2. #2
    Join Date
    Oct 2005
    alter table test modify t2 default null;
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  3. #3
    Join Date
    Nov 2006
    tomcat got there right before me

    He's right, only way is to change the default is to modify it to null (acts the same as if you didn't have it to begin with). And, in case you want it, here is the link to the oracle doc which states:
    If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL."

    (search for data_default on this page):
    Paul Barmaksezian
    Founder, Surfliner Services, LLC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.