An observation about default column value

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 3 Feb 2006 16:42:26 +0200
Message-ID: <6e49b6d00602030642w293f65ffw@mail.gmail.com>

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>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 03 2006 - 08:42:26 CST