Question: In Oracle, is there a way to remove the default of the column of a table ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jul 2004
    Posts
    21

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

    Hi,

    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
    Location
    Indianapolis
    Posts
    100
    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
    Posts
    13
    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:
    "Note:
    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):
    http://download-west.oracle.com/docs...htm#SQLRF01001
    Paul Barmaksezian
    Founder, Surfliner Services, LLC
    http://www.surflinerservices.com

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