Sequence as column default

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 10 Mar 2003 12:49:12 -0800
Message-ID: <F001.005651A7.20030310124912@fatcity.com>

Has anyone defined a sequence as the default value for a column? The manual is a little ambiguous (in my mind anyway):

In the 8.1.7 manual:
Restriction: A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.

In 9i this was altered to read:
Restriction on Default Column Values
A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

SQL> alter table test add
  2 (col3 number default addressID.NextVal);
(col3 number default addressID.NextVal)

                     *

ERROR at line 2:
ORA-00984: column not allowed here                                      

The alternative is to use an insert trigger, but it seems this would be more efficient. Since we are planning to use this a LOT, I thought I should try for a definate answer. Thanks for your patience.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Mar 10 2003 - 14:49:12 CST