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;

- ————-
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
    if$ is null then
        select t3_seq.nextval into$ from dual;
    end if;
show errors;

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


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;


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;


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.

Editor's Picks