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