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 ?

.
.
.

AIOUG Sangam 13

Registration for the All India Oracle User Group conference AIOUG Sangam 13 is now open.

It is in Hyderabad 08-09 November.

Do register and attend if you can.

.
.

RMAN (and DataPump) book

Kamran Agayev's and Aman Sharma's book "Oracle Backup and Recovery: Expert Secrets For Using RMAN & DataPump" is now available on amazon.   (Why do I put this on my blog ? I was one of the Technical Reviewers of the book, having reviewed and commented on selected chapters of the book before it was published).

.

.

.