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

[CMT]

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 ?

[tomcat]

alter table test modify t2 default null;

[pbarmak]

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