‘DEFAULT’ CLAUSE IN ‘CREATE TABLE’ STATEMENT

When a table will be updated or any data is inserted into the table, a default value can be used. At the beginning, the column’s value is set to value in create table statement with default clause. Also, the data type of the expression must match with the data type of the column It will be clear with a simple example.

First , a table named default_tbl is created without a default value in its columns:

SQL> create table default_tbl (x integer, y varchar2(10));
Table created

After creating the table, we insert a row, which its x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

When we select the table’s data to show, x column has the value 1, but y column is null:

SQL> select * from default_tbl;
X                             Y
————– ———-
1

Let’s drop our table and create again but now with a default clause for y column:

SQL> drop table default_tbl purge;
Table dropped

SQL> create table default_tbl (x integer, y varchar2(10) default ‘y’);
Table created

Once we insert a row with an x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

Finally, select table clause shows that table has a row with a y column value ‘y’ which we determined in create table statement:

SQL> select * from default_tbl;

X                         Y
————- ———-
1                         y