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
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
    host varchar2(256) default
    ip_address varchar2(256) default
    language varchar2(256) default
    protocol varchar2(200) default
    terminal varchar2(200) default
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
    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.

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays