An observation about default column value
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- To: oracle list <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Feb 2006 16:42:26 +0200
We had an app where some columns had default values. So life changes
and now we don't need them. Question is how to get rid of them?
Of course one can alter table with default value null. It seems that
functionally result is the same as without ever declared default value
for the column although data dictionary return different results...
Any comments? ;)
Below is code for 9.2.0.5 Oracle where
1) initially table is created without default column clause
2) altered to assign default column value
3) altered to assign default column value null
SQL> create table qaqa (a number);
Table created.
SQL> insert into qaqa values (default);
1 row created.
SQL> select * from qaqa;
A
----------
1 row selected.
SQL> select table_name, column_name, data_default from
user_tab_columns where table_name = 'QAQA';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_DEFAULT
--------------------------------------------------------------------------------
QAQA A
1 row selected.
SQL> alter table qaqa modify (a number default 1);
Table altered.
SQL> insert into qaqa values (default);
1 row created.
SQL> select * from qaqa;
A
----------
1
2 rows selected.
SQL> select table_name, column_name, data_default from
user_tab_columns where table_name = 'QAQA';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_DEFAULT
--------------------------------------------------------------------------------
QAQA A
1
1 row selected.
SQL> alter table qaqa modify (a number default null);
Table altered.
SQL> insert into qaqa values (default);
1 row created.
SQL> select * from qaqa;
A
----------
1
3 rows selected.
SQL> select table_name, column_name, data_default from
user_tab_columns where table_name = 'QAQA';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_DEFAULT
--------------------------------------------------------------------------------
QAQA A
null
1 row selected.
SQL>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
