Data Management

Oracle Tip: How to use default values with database columns

Using default values on database columns helps to insulate database design issues from application code. Find out how to change a column's default value at a later date using a single command.
This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

When you create a database table, you have the option to specify a DEFAULT value. Using default values on database columns helps to insulate database design issues from application code.

You can change the default value of a column at some later date with a single ALTER TABLE command and application code will immediately start using the new values.

It's important to remember that DEFAULT values are only used when a column isn't specified in an INSERT or MERGE, or when the DEFAULT keyword is used. If you don't explicitly declare a DEFAULT value, Oracle implicitly defines the default value to be NULL, and the DEFAULT keyword will even work with such. Since Oracle 9i, you may use pseudocolumns like SYSDATE or CURRENT_TIMESTAMP in DEFAULT clauses. For example:

create table t1
(
    id$ integer not null,
    charcol char default 'Y',
    datecol date default sysdate,
    strcol varchar2(30) default user,
    intcol integer default 12
);
insert into t1 (id$) values (1);
select * from t1;

       ID$ C DATECOL   STRCOL                             INTCOL
---------- - --------- ------------------------------ ----------
         1 Y 28-MAY-04 SCOTT                                  12

The DEFAULT keyword from INSERT, MERGE, or UPDATE syntax may seem unnecessary, but consider the case where you wish to insert a row using all the default values. Oracle will not accept INSERT INTO <table> or INSERT INTO <table> VALUES () as valid SQL. You must specify at least one column, but you may use the DEFAULT keyword to allow the default value rather than hard-coding a value, so the following is valid syntax that will create a row with all DEFAULT values.

create table t2(charcol char default 'Y',datecol date default sysdate);
insert into t2 (charcol) values (default);
select * from t2;

C DATECOL
- ---------
Y 28-MAY-04

One very common problem is to emulate the Autonumber functionality of other database vendors where a column is automatically populated with some kind of sequence number. In Oracle databases, you cannot specify a sequence number as a DEFAULT value for a column; however, you can emulate this functionality using a trigger. Even if a column is declared NOT NULL, you can still omit the column from INSERT statements to be populated in the trigger. Notice the use of the DEFAULT keyword rather than an explicit NULL for readability:

create sequence t3_seq;
create table t3(id$ integer constraint t3_pk primary key);
create or replace trigger t3_autonumber
before insert on t3 for each row
begin
    if :new.id$ is null then
        select t3_seq.nextval into :new.id$ from dual;
    end if;
end;
/
show errors;

insert into t3(id$) values (default);
select * from t3;

       ID$
----------
         1

You can populate columns with default values from the set of SYS_CONTEXT values and collect important information about a session somewhere:

create table t4
(
    when date default SYSDATE,
    db_domain varchar2(200) default SYS_CONTEXT('USERENV','DB_DOMAIN'),
    host varchar2(256) default SYS_CONTEXT('USERENV','HOST'),
    ip_address varchar2(256) default SYS_CONTEXT('USERENV','IP_ADDRESS'),
    language varchar2(256) default SYS_CONTEXT('USERENV','LANGUAGE'),
    protocol varchar2(200) default SYS_CONTEXT('USERENV','NETWORK_PROTOCOL'),
    terminal varchar2(200) default SYS_CONTEXT('USERENV','TERMINAL')
);
insert into t4 (when) values (default);
select * from t4;

WHEN
---------
DB_DOMAIN
------------------------------------------------------------------------------
HOST
------------------------------------------------------------------------------
IP_ADDRESS
------------------------------------------------------------------------------
LANGUAGE
------------------------------------------------------------------------------
PROTOCOL
------------------------------------------------------------------------------
TERMINAL
------------------------------------------------------------------------------
28-MAY-04
scott.bn
MSHOME\SCOTT-LAP
AMERICAN_AMERICA.AL32UTF8
SCOTT-LAP

You can also use the new pseudocolumn SYS_GUID to populate a column; it has the advantage of being globally unique and doesn't require the sequence number or trigger overhead:

create table t5(id$ raw(16) default sys_guid()
    constraint t5_pk primary key);
insert into t5(id$) values (default);
select * from t5;

ID$
--------------------------------
643718A07DCC43F2AC95312FD43617BA

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.

4 comments
praveenk053
praveenk053

Hi, I want SQL query to check default value is zero for a column in a particular table.So pls provide me the SQL query

shahid.navab
shahid.navab

hi,my name is mohd shahid...i am a oracle developer ,,,i want to know how to use default values in existing tables using alter table...

Tumie
Tumie

iam having a problem with numbers starting with zeros e.g 00123.After typing that zero disapears.how do i set numbers that start with zero more so that,that zero appears in tables?

gurujothi37
gurujothi37

Hi Praveen, for this requirement you can use Check constraint. something like, ALTER TABLE table_name add CONSTRAINT constraint_name CHECK (column_name condition)