Add DEFAULT Value to Oracle Table

In this post, we will discuss how to add default value in the column of the Oracle database table? What is the ALTER table ADD column Oracle DEFAULT value? How to modify default value? What is the default on the null clause?

In the Oracle database, if we want to insert a default value into a table column then we are using the DEFAULT Clause. The default value must match with the data type of that particular column. Syntax:- columnname datatype(size) DEFAULT default_value;

Example:-

SQL> CREATE TABLE test (
       name VARCHAR2(10),
       sal NUMBER(10) DEFAULT 5000
     );
Table created.

SQL> INSERT INTO test(name) VALUES('abc');
1 row created.

SQL> SELECT * FROM test;
NAME              SAL
---------- ----------
abc              5000

Another Example:-

SQL> CREATE TABLE test01 (
       name VARCHAR2(10),
       id NUMBER(10) DEFAULT 100,
       address VARCHAR2(15) DEFAULT 'london'
     );
Table created.

SQL> INSERT INTO test01(name) VALUES('sophia');
SQL> INSERT INTO test01(name, id) VALUES('alex', 10);
SQL> INSERT INTO test01 VALUES('william', 20, 'chester');
SQL> SELECT * FROM test01;
NAME               ID ADDRESS
---------- ---------- ---------------
sophia            100 london
alex               10 london
william            20 chester

Enable Default Clause on an Existing column

We can also add a default clause on an existing column of a table. Whenever we are coping a table from another table then only its data or records are copied. To assign the default clause again, we need to enable the default clause on the particular column.

Syntax to enable DEFAULT clause on an existing column the table,
ALTER TABLE tableName
MODIFY columnName
DEFAULT default-value;

Example:-

SQL> CREATE TABLE test02
     AS
     SELECT * FROM test;
Table created.

SQL> SELECT * FROM test02;
NAME              SAL
---------- ----------
abc              5000

SQL> INSERT INTO test02(name) VALUES('xyz');
1 row created.

SQL> SELECT * FROM test02;
NAME              SAL
---------- ----------
abc              5000
xyz

The TEST table had a DEFAULT clause on the SAL column, but test02 is copied from the test table. That’s why the DEFAULT clause is not assigned to the SAL column. We need to assign it explicitly.

SQL> ALTER TABLE test02 MODIFY sal DEFAULT 5000;
Table altered.

SQL> INSERT INTO test02(name) VALUES('pqr');
1 row created.

SQL> SELECT * FROM test02;
NAME              SAL
---------- ----------
abc              5000
xyz
pqr              5000

ALTER Table ADD Column Oracle DEFAULT Value

In the Oracle database, we can also add a new column to the table with a DEFAULT value. For this Syntax is,
ALTER TABLE tableName
ADD newColumnName datatype(size)
DEFAULT default-value;

Example:- Add a new column “address” on the test02 column and set the default value “London”.

SQL> SELECT * FROM test02;
NAME              SAL
---------- ----------
abc              5000
xyz
pqr              5000

SQL> ALTER TABLE test02 ADD 
     address VARCHAR2(10) DEFAULT 'London';
Table altered.

SQL> SELECT * FROM test02;
NAME              SAL ADDRESS
---------- ---------- ----------
abc              5000 London
xyz                   London
pqr              5000 London

The address value for existing rows becomes ‘London’ and whenever we add new row without address value then ‘London’ will be inserted by default.

DEFAULT ON NULL Clause

In all relational databases always null values override default values. To overcome this problem oracle12c introduced the DEFAULT ON NULL clause, which is used to insert default values automatically when we are specifying null values also. Syntax:- columname datatype(size) DEFAULT ON NULL default_value;

Example:-

SQL> CREATE TABLE test03 (
       name VARCHAR2(10),
       city VARCHAR2(10)
       DEFAULT ON NULL 'Texas'
     );
Table created.

SQL> INSERT INTO test03(name, city) 
     VALUES('Olivia', null);
1 row(s) inserted.

SQL> SELECT * FROM test03;
NAME	CITY
------  -----
Olivia	Texas

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

Leave a Reply