10 October, 2013

The DEFAULT value for a column

Here's a simple demo of how a column's DEFAULT definiton behaves.

SQL> create table test_default (id_col number, data_col varchar2(5));

Table created.

SQL> insert into test_default values (1,NULL);

1 row created.

SQL> alter table test_default modify (data_col default 'YES');

Table altered.

SQL> insert into test_default select 2,'TWO' from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO

SQL> insert into test_default (id_col) select 3 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES

SQL> alter table test_default modify (data_col default NULL);

Table altered.

SQL> insert into test_default (id_col) select 4 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES
         4

SQL> 

So, for row 2, if a value is specified for the column, it overrides the default.
For row 3, when a value is NOT specified, the default applies.
The "modify (data_col default NULL)" allows me to "reset" the DEFAULT definition to allow NULLs.  That is how row 4 inserts a NULL.

Question : What if the column has a DEFAULT "YES" and I run :
insert into test_default select 2, NULL from dual;
Will the DEFAULT override the NULL ?

.
.
.

No comments: