Add DEFAULT Value to Oracle Table

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 the 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 the data type of that particular column. Syntax:- columnname datatype(size) DEFAULT default_value;

Example:-

CREATE TABLE test (
  name VARCHAR2(10),
  sal NUMBER(10) DEFAULT 5000
);

INSERT INTO test(name) VALUES('abc');

SELECT * FROM test;

Output:-

NAME              SAL
---------- ----------
abc              5000

Another Example:-

CREATE TABLE test01 (
  name VARCHAR2(10),
  id NUMBER(10) DEFAULT 100,
  address VARCHAR2(15) DEFAULT 'london'
);

INSERT INTO test01(name) VALUES('sophia');
INSERT INTO test01(name, id) VALUES('alex', 10);
INSERT INTO test01 VALUES('william', 20, 'chester');

SELECT * FROM test01;

Output:-

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 copying 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 of the table,
ALTER TABLE tableName
MODIFY columnName
DEFAULT default-value;

Example:-

CREATE TABLE test02
AS
SELECT * FROM test;

SELECT * FROM test02;

Output:-

NAME              SAL
---------- ----------
abc              5000

INSERT INTO test02(name) VALUES('xyz');
SELECT * FROM test02;

Output:-

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.

ALTER TABLE test02 MODIFY sal DEFAULT 5000;
INSERT INTO test02(name) VALUES('pqr');
SELECT * FROM test02;

Output:-

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”.

SELECT * FROM test02;

Output:-

NAME              SAL
---------- ----------
abc              5000
xyz
pqr              5000

ALTER TABLE test02 ADD 
address VARCHAR2(10) DEFAULT 'London';

SELECT * FROM test02;

Output:-

NAME              SAL ADDRESS
---------- ---------- ----------
abc              5000 London
xyz                   London
pqr              5000 London

The address value for existing rows becomes ‘London’. And whenever we add a new row without an 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:-

CREATE TABLE test03 (
  name VARCHAR2(10),
  city VARCHAR2(10)
  DEFAULT ON NULL 'Texas'
);

INSERT INTO test03(name, city) 
VALUES('Olivia', null);

SELECT * FROM test03;

Output:-

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 do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search