12c Database : Default Values enhancements

Prior to 12c, if the developer wants to create a sequential ID number generator , he/she has to use a sequence and then a trigger or reference that seq value in the insert statement.

From 12c Onwards, this has been enhanced by providing the sequence.nextval in the table definition itself.

Further it even enhanced that without sequence you can now generate a sequential number with identity columns, where this kind of feature available in MS-SQL etc.

Let’s see the default value enhancements.

	SQL> create sequence t1_seq; 
	Sequence created. 

	SQL> create table t1 (id number default t1_seq.nextval, name varchar2(20)) tablespace users; 
	Table created. 

	SQL> insert into t1(id,name) values(1,'Geek DBA'); 
	1 row created. 

	SQL> insert into t1(name) values('Ramesh'); 
	1 row created. 


	SQL> insert into t1(id,name) values(NULL,'Brijest'); 
	1 row created. 

	SQL> select * from t1;

		ID NAME
	---------- --------------------
		 1 Geek DBA
		 1 Ramesh
		   Brijesh

	SQL>

As you see there we have inserted the rows with ID, without ID and with NULL and if you observe the output, ID populates

	1) For first insert, the statement has been inserted as is
	2) For second insert, Used sequence.nextval and inserted 1 in ID column
	3) For third insert, as we used NULL for ID, NULL has been inserted.

In addition to above we can use explicitly sequences where NULL has been provided like for second case above. Let test with what happened when nulls added, as I have

	a) Created a sequence
	b) Add a column to the table and made that column default value to a sequece, note the difference is 
	   default on null (this is new)
	c) First insert having all columns and inserted as is
	d) Second insert with no sequences appended
	e) Third insert wiht null values, as you see the ID1 column is not populated but the ID2 

with default on null sequence is populated with value

	SQL> truncate table t1;

	Table truncated.

	SQL> create sequence t1_seq_on_null;

	Sequence created.

	SQL> alter table t1 add id2 number default on null t1_seq_on_null.nextval;

	Table altered.

	SQL> insert into t1(id,name,id2) values(1,'Geek DBA',101);

	1 row created.

	SQL> insert into t1(name) values('Geek DBA');

	1 row created.

	SQL> insert into t1(id,name,id2) values(null,'Geek DBA',null);

	1 row created.

	SQL> commit;

	Commit complete.

	SQL> select * from t1;

		ID NAME                        ID2
	---------- -------------------- ----------
		 1 Geek DBA                      101
		21 Geek DBA                        1
		   Geek DBA                        2