Oracle 12c New Features for table Defaults

With Oracle 12c there is improvements in tables default values.

Starting on Oracle 12c you can use sequence nextval straight in column default or you can use "generated as identity" for column default (this will generate new sequence for table and start use it for column default values) This new sequence is linked with table so if you remove the table also the sequence is removed. You can also use "generated by default as identity" when you create new table sequence. This will allow you to override sequence value in inserts. If you use default word as column value or leave column and it's value out of the insert the sequence is used but you can also give new value for this column when "by default" is used.

With Oracle 12c you can also give default values for null columns. So if you are inserting null value into column that have for example this default settings: "default ON NULL 11" then 11 value will be inserted into table instead of null.

Examples:

Create table clause with using existing sequence:
SQL> CREATE TABLE TEST_TABLE ( id int seq_test_id.nextval primary key, name varchar2(32) );


Create table clause with generating new identity sequence:
SQL> CREATE TABLE TEST_TABLE ( id int generated as identity primary key, name varchar2(32) );

Create table clause with generating new identity sequence with sequence init values:
SQL> CREATE TABLE TEST_TABLE( id int generated as identity (start with 10000) primary key, name varchar2(32) );

Override sequence value when table squence is created with "generated by default as identity":
SQL> INSERT INTO TEST_TABLE (id, name) VALUES ( 10, 'test_name');

Using sequence value when table squence is created with "generated by default as identity":
SQL> INSERT INTO TEST_TABLE (id, name) VALUES ( default, 'test_name');
or
SQL> INSERT INTO TEST_TABLE (name) VALUES ( 'test_name');