Oracle column self-increment implementation (3)-default Values Using sequences

In Oracle 12c, you can use sequences of and NEXTVALCURRVAL的值作为默认值，来实现列自增！

One, the use of the sequence of NEXTVALand CURRVAL 的值作为默认值

Create a sequence

CREATE SEQUENCE T1_seq;

Build table

CREATE TABLE  numberDEFAULT VARCHAR2 (());

Inserting data

INSERT  intoT1 (description)VALUES('DESCRIPTION only');INSERT  intoT1 (ID, description)VALUES(999,'id=999 and DESCRIPTION');INSERT  intoT1 (ID, description)VALUES(NULL,'Id=null and DESCRIPTION');

Query results

SELECT *  from T1;

Second, the default value is explicitly non-null

Create two sequences

CREATE SEQUENCE default_seq; CREATE SEQUENCE Default_on_null_seq;

Tables, col1 and col2 use the nextval of the above two sequences as default values, where col2 default on NULL

CREATE TABLE  numberdefault number default on NULL  VARCHAR2(());

Inserting data

INSERT  intoT2 (description)VALUES('DESCRIPTION only');INSERT  intoT2 (col1, col2, description)VALUES(999,999,'999,999,description');INSERT  intoT2 (col1, col2, description)VALUES(NULL,NULL,'null,null,description');

Query data, you can see the col2 bit null when the default conversion used Default_on_null_seq. Nextval's

SELECT *  from T2;

Example: A simple example of master-slave table

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;

SELECT * from detail;

Original:

DEFAULT Values for Table columns:enhancements in Oracle Database 12c Release 1 (12.1)

Oracle column self-increment implementation (3)-default Values Using sequences