Alter table to modify default value of column

Question

I have a requirement where we need to modify a column's default value in database table. The table is already an existing table in database and currently the default value of the column is NULL. Now if add a new default value to this column, If I am correct it updates all the existing NULLs of the column to new DEfault value. Is there a way to not to do this but still set a new default value on column. I mean I do not want the existing NULLs to be updated and want them to remain as NULLs.

Any help on this is appreciated. Thanks

Answer 1

Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table.

I create a table with a column col2 that has no default value

SQL> create table foo(
  2    col1 number primary key,
  3    col2 varchar2(10)
  4  );

Table created.

SQL> insert into foo( col1 ) values (1);

1 row created.

SQL> insert into foo( col1 ) values (2);

1 row created.

SQL> insert into foo( col1 ) values (3);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

If I then alter the table to set a default value, nothing about the existing rows will change

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'foo' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

SQL> insert into foo( col1 ) values (4);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

Even if I subsequently change the default again, there will still be no change to the existing rows

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'bar' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

SQL> insert into foo( col1 ) values (5);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo
         5 bar