Adding a default value to a column on a table

In oracle there is two ways by which you can add a default value to a column. This means if you don't provide any value to the column that column will get automatically a default value. With ALTER TABLE ... ADD statement and ALTER TABLE ... MODIFY statement you can provide a default value to a column. First one is for adding a new column and then assigned it to a default value and second one is modify existing column to a default value. Below is the description along with examples.

1)ALTER TABLE ... ADD Statement:
With
ALTER TABLE table_name ADD column_name data_type DEFAULT default_value
you can add a column provided a default value to the column. If you add a default value in this way Oracle Database updates each row in the new column with the value you specify for DEFAULT. As it is an update operation so it, in turn, fires any AFTER UPDATE triggers defined on the table.

SQL> create table test_default (col1 number);
Table created.

SQL> insert into test_default values(1);
1 row created.

SQL> insert into test_default values(2);
1 row created.

SQL> select * from test_default;
COL1
----------
1
2

SQL> alter table test_default add col2 varchar2(10) default 'Dhaka';
Table altered.

SQL> select * from test_default;
COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
We see every column of col2 gets a default value which means every column is updated.

You can see the default value of a column from user_tab_columns view or from user_tab_cols or cols synonym.

SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
'Dhaka' COL2

Always remember once you assign a default value to a column you can never remove the default value of the column completely. You can assign the default value to NULL but still data dictionary will show it NULL. Below is the example.

The following statement has no effect in changing the default value and hence following statement is useless.

SQL> alter table test_default modify col2 varchar2(10);

Table altered.

In the data dictionary it will display the default value.
SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
'Dhaka' COL2

In order to assign default value to NULL issue,
SQL> alter table test_default modify col2 varchar2(10) default NULL;

Table altered.

Now data dictionary will display NULL in the data_default field.
SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
NULL COL2

In the column NULL value will be inserted if no value is given.
SQL> insert into test_default(col1) values(3);

1 row created.

SQL> select * from test_default;

COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
3
2)ALTER TABLE ... MODIFY statement:
With,
ALTER TABLE table_name MODIFY column_name DEFAULT default_value
you can modify a column value to the default one. But like ALTER TABLE ... ADD, MODIFY will not update the all the column values in the table. Subsequent insert will be assigned to default value if no value is provided.

SQL> alter table test_default modify col2 DEFAULT 'Jhenidah';

Table altered.

SQL> select * from test_default;

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

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

1 row created.

SQL> select * from test_default;

COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
3
4 Jhenidah
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html