8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
This article describes the changes to table column defaults in Oracle Database 12c.
Related articles.
- DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c
- AutoNumber, Identity and Using Sequences as Default Values in Oracle
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- AutoNumber And Identity Functionality in Oracle Databases (Pre 12c)
DEFAULT Values Using Sequences
In Oracle 12c, it is now possible to specify the CURRVAL
and NEXTVAL
sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose.
In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL
clause described in the next section.
CREATE SEQUENCE t1_seq; CREATE TABLE t1 ( id NUMBER DEFAULT t1_seq.NEXTVAL, description VARCHAR2(30) ); INSERT INTO t1 (description) VALUES ('DESCRIPTION only'); INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); SELECT * FROM t1; ID DESCRIPTION ---------- ------------------------------ 1 DESCRIPTION only 999 ID=999 and DESCRIPTION ID=NULL and DESCRIPTION 3 rows selected. SQL>
The fact we can use both the NEXTVAL
and CURRVAL
pseudocolumns gives us the ability to auto-populate master-detail relationships, as shown below.
CREATE SEQUENCE master_seq; CREATE SEQUENCE detail_seq; CREATE TABLE master ( id NUMBER DEFAULT master_seq.NEXTVAL, description VARCHAR2(30) ); CREATE TABLE detail ( id NUMBER DEFAULT detail_seq.NEXTVAL, master_id NUMBER DEFAULT master_seq.CURRVAL, description VARCHAR2(30) ); INSERT INTO master (description) VALUES ('Master 1'); INSERT INTO detail (description) VALUES ('Detail 1'); INSERT INTO detail (description) VALUES ('Detail 2'); INSERT INTO master (description) VALUES ('Master 2'); INSERT INTO detail (description) VALUES ('Detail 3'); INSERT INTO detail (description) VALUES ('Detail 4'); SELECT * FROM master; ID DESCRIPTION ---------- ------------------------------ 1 Master 1 2 Master 2 2 rows selected. SQL> SELECT * FROM detail; ID MASTER_ID DESCRIPTION ---------- ---------- ------------------------------ 1 1 Detail 1 2 1 Detail 2 3 2 Detail 3 4 2 Detail 4 4 rows selected. SQL>
Of course, this would only make sense if you could guarantee the inserts into the detail table would always immediately follow the insert into the master table, which would prevent you from using bulk-bind operations.
A few things to remember about using sequence pseudocolumns as defaults include:
- During table creation, the sequence must exist and you must have select privilege on it for it to be used as a column default.
- The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table.
- If the sequence is dropped after table creation, subsequent inserts will error.
- Sequences used as default values are always stored in the data dictionary with fully qualified names. Normal name resolution rules are used to determine the sequence owner, including expansion of private and public synonyms.
- As with any use of a sequence, gaps in the sequence of numbers can occur for a number of reasons. For example, if a sequence number is requested and not used, a statement including a sequence is rolled back, or the databases is turned off and cached sequence values are lost.
DEFAULT Values On Explicit NULLs
In the previous section we saw default values are only used when a column is not referenced in an insert statement. If the column is referenced, even when supplying the value NULL, the default value is not used. Oracle 12c allows you to modify this behaviour using the ON NULL
clause in the default definition.
The following example compares the default action of the DEFAULT
clause, with that of DEFAULT ON NULL
. The example uses sequences to populate two columns, one using the standard DEFAULT
clause, the other using the DEFAULT ON NULL
clause.
CREATE SEQUENCE default_seq; CREATE SEQUENCE default_on_null_seq; CREATE TABLE t2 ( col1 NUMBER DEFAULT default_seq.NEXTVAL, col2 NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL, description VARCHAR2(30) ); INSERT INTO t2 (description) VALUES ('DESCRIPTION only'); INSERT INTO t2 (col1, col2, description) VALUES (999, 999, '999,999,DESCRIPTION'); INSERT INTO t2 (col1, col2, description) VALUES (NULL, NULL, 'NULL,NULL,DESCRIPTION'); SELECT * FROM t2; COL1 COL2 DESCRIPTION ---------- ---------- ------------------------------ 1 1 DESCRIPTION only 999 999 999,999,DESCRIPTION 2 NULL,NULL,DESCRIPTION 3 rows selected. SQL>
Notice the difference in the way the explicit NULL is handled.
One other thing to consider is the DEFAULT ON NULL
implicitly sets the column to mandatory.
desc t2 Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 NUMBER COL2 NOT NULL NUMBER DESCRIPTION VARCHAR2(30) SQL>
In Oracle 23c we can define a column as DEFAULT ON NULL FOR INSERT AND UPDATE
. This replaces explicit null values with the default value in update statements.
Metadata-Only DEFAULT Values
Prior to Oracle 11g, adding a new column to an existing table required all rows in that table to be modified to add the new column.
Oracle 11g introduced the concept of metadata-only default values. Adding a NOT NULL
column with a DEFAULT
clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition.
Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a DEFAULT
clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as NOT NULL
or not. This represents both a space saving and performance improvement.
There are some fairly obvious restrictions on this functionality, but it's worth checking the ALTER TABLE : DEFAULT section of the manual to make sure you are not hitting one.
For more information see:
- CREATE TABLE : DEFAULT
- ALTER TABLE : DEFAULT
- DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c
- AutoNumber, Identity and Using Sequences as Default Values in Oracle
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- AutoNumber And Identity Functionality in Oracle Databases (Pre 12c)
Hope this helps. Regards Tim...