Re: Q: how to alter table modify column default ?

In article <8h6m9c$hgm$1_at_nnrp1.deja.com>,   tedchyn_at_yahoo.com wrote:
> Sir,
> I have a table with column col_x null default 'M'.
> How do I alter table modify column default ... so col_x becomes null
> with no default value ?
> After executing alter table modify column null or '',
> default_data in user_tab_columns show 'null'(string) or ''
 respectively
> instead of null.
>
> thanks in advacne.
> ted chyn
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

It is a trick question. You want to know how to remove a default -- but you cannot. All you can do is set the default value be what would be placed there in the event there is no default (NULL).

Also, you say "contain value of literal 'null' and '' instead of null" which is technically not accurate. The data_default contains the word NULL not 'NULL' -- it does not have a literal, it has the keyword NULL. The data_default for a column is some SQL Expression that will (in effect) be select'ed from DUAL at runtime. Consider this example:

ops$tkyte_at_8i> create table t
  2 ( a int,
  3 x int default 1,
  4 y varchar2(5) default 'M',
  5 z date default sysdate
  6 )
  7 /
Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select column_name, data_default   2 from user_tab_columns
  3 where table_name = 'T';

COLUMN_NAME                    DATA_DEFAU
------------------------------ ----------
A
X                              1
Y                              'M'
Z                              sysdate

See how sysdate is in there -- we will "select sysdate from dual" at runtime to supply a default for this column -- we won't put the word SYSDATE into it

ops$tkyte_at_8i> insert into t (a) values (1); 1 row created.

ops$tkyte_at_8i> alter table t modify x default null; Table altered.

ops$tkyte_at_8i> alter table t modify y default null; Table altered.

ops$tkyte_at_8i> alter table t modify z default null; Table altered.

ops$tkyte_at_8i> select column_name, data_default   2 from user_tab_columns
  3 where table_name = 'T';

COLUMN_NAME                    DATA_DEFAU
------------------------------ ----------
A
X                              null
Y                              null
Z                              null

Now, each of the columns has a default still (cannot get rid of it) and that default is now "select NULL from dual" -- which is the same as not having a default. It will not put the word 'NULL' into the value but rather the value NULL into it.

ops$tkyte_at_8i> insert into t (a) values (2);

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from t;

         A X Y Z
---------- ---------- ----- ---------

         1          1 M     02-JUN-00
         2

See -- they are all null...

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.