8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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 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:

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:

Hope this helps. Regards Tim...

Back to the Top.