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.
