Live SQL

Generating a Default Value from a SEQUENCE

View All Scripts Login to Run Script

Script Name Generating a Default Value from a SEQUENCE
Description Referencing a sequence as a column default value in a create table statement. New with database 12c.
Area Oracle 12c
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1
First we will need a sequence to reference in our create table statement.
create a sequence
```
create sequence db_id_test_seq
```
```
Sequence created.
```
Statement 2
Creating a table, note the use of default and primary key. The ability to reference a sequence as a default is new with database 12c. The specification of the column as a primary key will make the column not null and unique.
create a table defaulting to a sequence
```
create table db_12c_style_identity 
(  
id               integer  DEFAULT ON NULL db_id_test_seq.nextval primary key, 
another_column   varchar2(30) 
)
```
```
Table created.
```
Statement 3
Insert a row without specifying the ID column. This will allow the column to default to the value from the sequence.
```
insert into db_12c_style_identity (another_column) values ('hello')
```
```
1 row(s) inserted.
```

Statement 4

View table data

select * from db_12c_style_identity

 
ID ANOTHER_COLUMN
1 hello

Statement 5
Manually set a value for the ID column during insert. Note this is risky as a future insert statement may fail due to primary key uniqueness constraint.
```
insert into db_12c_style_identity (id, another_column) values (22, 'hello')
```
```
1 row(s) inserted.
```

Statement 6

View table data

select * 
from db_12c_style_identity

 
ID ANOTHER_COLUMN
1 hello
22 hello

2 rows selected.

Statement 7
Without specifying ON NULL in the create table statement this insert would fail. The ON NULL allows the default to fire even when the column is specified in an insert statement.
```
insert into db_12c_style_identity (id, another_column) values (null, 'goodbye')
```
```
1 row(s) inserted.
```

Statement 8

View table data.

select * from db_12c_style_identity

 
ID ANOTHER_COLUMN
1 hello
22 hello
2 goodbye

3 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database