Oracle 12c Identity

Before 12c, if you want to achieve the automatic growth of column, usually through the sequence, but this problem is resolved in 12c, Oracle deferred the identity column function, which solves the previous problem perfectly, here is my simple test, For reference!

Sql>Alter pluggable databaseAllOpen Pluggable database altered. Sql>Select Name,open_modeFrom V$pdbs;name Open_mode----------------------------------------pdb$seed Read ONLYPDBORCL read writesql> conn Roger/[email protected] Connected.sql>Show Con_namecon_name------------------------------PDBORCLSql>CreateTable Test (IDNumber generatedByDefaultAsIdentity,2 Name VARCHAR2 (20)); Table created. Sql>Insertinto Test (name)Values' Roger '); 1 row created. Sql>Insertinto Test (name)Values' killdb.com '); 1 row created. Sql>CommitCommit complete.sql> select * from test; ID name------------------------------1 Roger 2 killdb.comsql>sql> insert into test (Id,name) values (null, ' killdb.com '); insert into test (id,name) values ( null, ' killdb.com ') *error at line 1:ora-01400:cannot insert  NULL into ( "ROGER".  " TEST ".  "ID")              

You can see that the ID column will automatically grow by default, and before 12c, Oracle can only do this with sequence. We can also see that, in this case, a null value cannot be inserted.

1:ora-value

So is it really possible to insert a null value for the identity column? Actually, but you have to do this:

Sql>CreateTable Test1 (IDNumber generatedByDefaultOnNullAsIdentity, name VARCHAR2 (20)); Table created. Sql>sql>InsertInto Test1values (1, ' killdb.com '); 1 row created. sql> insert into test1 values (2, ' baidu.com '); 1 row created. sql> insert into test1 values (null, ' google.com '); 1 row created. sql> commit; commit complete. sql> select * from test1; ID name------------------------------1 killdb.com 2 baidu.com 1 google.com    

But strangely enough, as you can see, if the ID of the inserted 3rd data column is NULL, Oracle's own actual value is 1. How did this 1 come from? I'll tell you the answer later.

Set id=where id=commit;  Commit complete. From Test1; ID NAME------------------------------1 killdb.com baidu.com 1 google.com    

In this case, normal DML can be done because the identity column is null by default, and a test continues below.

Sql>CreateTable Test2 (IDNumber generated alwaysAsIdentity, name VARCHAR2 (20)); Table created. Sql>Insertinto Test2 (name)Values' killdb.com '); 1 row created. Sql>Insertinto Test2 (name)Values' baidu.com '); 1 row created. Sql>Insertinto Test2 (name)Values' google.com '); 1 row created. Sql>CommitCommit complete.Sql>SELECT *from Test2; ID NAME------------------------------1 killdb.com 2 baidu.com 3 google.comsql>Update Test2Set id=4where id=2;Update Test2Set id=4where id=2*errorAt line1:ora-32796:cannotUpdate a generated alwaysIdentityColumnSql>Update Test2Set id=1where id=2;Update Test2set id=1 where id=2 *error at line 1:ora-32796:cannot span class= "keyword" >update a generated always identity columnsql> delete from test2 where id=< Span class= "number" >2;1 row deleted. sql> commit; commit complete. sql> select * from test2; ID name------------------------------1 killdb.com 3 google.com      

As we can see, it's impossible to update, and here we should know that Oracle won't allow you to update.

Sql>sql>Select table_name,2 column_name,3 Default_on_null,4 Identity_column,5 Data_default6From User_tab_columns7where identity_column=' YES '; TABLE_NAME column_name DEF IDE Data_default----------------------------------------------------------------------------------------------------------TEST ID NO YES "ROGER". " iseq$$_91820 ". NextvalTEST1 ID Yes yes" ROGER "." iseq$$_91822 ". NextvalTEST2 ID NO YES" ROGER "." iseq$$_91824 ".nextvalsql> sql>Set pagesizeLong9999Sql>Select DBMS_METADATA.GET_DDL (' TABLE ',' TEST ')From Dual;dbms_metadata. GET_DDL (' TABLE ', ' TEST ')--------------------------------------------------------------------------------CREATETABLE"ROGER"."TEST" ("ID"Number GENERATEDByDEFAULTAsIDENTITY MINVALUE1 MAXVALUE9999999999999999999999999999 INCREMENTBy1STARTWith1 CACHENoorder nocycleNotNULL ENABLE,"NAME" VARCHAR2 ()) SEGMENT CREATIONIMMEDIATE PCTFREETen pctusedInitrans1 Maxtrans255 nocompress LOGGING STORAGE (INITIAL65536NEXT1048576 minextents1 MAXEXTENTS2147483645 Pctincrease0 freelists1 FREELIST GROUPS1 Buffer_poolDEFAULT Flash_cacheDEFAULT Cell_flash_cacheDEFAULT) tablespace"USERS"Sql>Select DBMS_METADATA.GET_DDL (' TABLE ',' TEST1 ')From Dual;dbms_metadata. GET_DDL (' TABLE ', ' TEST1 ')--------------------------------------------------------------------------------CREATETABLE"ROGER"."TEST1" ("ID"Number GENERATEDByDEFAULTOnNullAsIDENTITY MINVALUE1 MAXVALUE9999999999999999999999999999 INCREMENTBy1STARTWith1 CACHE20 noorder nocycle not null ENABLE, " "NAME" VARCHAR2 (20)) SEGMENT CREATION immediate PCTFREE 10 pctused 40 Initrans 1 Maxtrans 255 Nocompress LOGGING STORAGE (INITIAL 65536 next  1048576 minextents 1 MAXEXTENTS 2147483645 pctincrease 0 freelists 1 FREELIST GROUPS 1 Buffer_pool default flash_cache default cell_flash_cache default) Tablespace  "USERS"           

Finally, with the definition of the table, we can see that the ID column is defined by default in order to sequence, which is similar to the property created in our previous version of sequence. And we see that by default the start with is 1. This is the reason why id=1 was inserted in the case of NULL earlier.

Oracle 12c Identity