Study Notes for identity column in Oracle 12c

Before 12c, if you want to achieve automatic column growth, it is usually achieved through sequence. However, this problem has been solved in 12c, and Oracle has postponed the identity column function, this function perfectly solves previous problems. The following is my simple test for your reference!

SQL> alter pluggable database all open;
 
Pluggable database altered.
 
SQL> select name, open_mode from V $ pdbs;
 
NAME OPEN_MODE
----------------------------------------
PDB $ SEED READ ONLY
PDBORCL READ WRITE
SQL> conn roger/roger @ pdborcl
Connected.
SQL> show con_name
 
CON_NAME
------------------------------
PDBORCL
SQL> create table test (id number generated by default as identity,
2 name varchar2 (20 ));
 
Table created.
 
SQL> insert into test (name) values ('Roger ');
 
1 row created.
 
SQL> insert into test (name) values ('111cn. Net ');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test;
 
ID NAME
------------------------------
1 roger
2 111cn.net
 
SQL>
SQL> insert into test (id, name) values (null, '111cn. Net ');
Insert into test (id, name) values (null, '111cn. Net ')
                                 *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ROGER". "TEST". "ID ")

We can see that the id column can automatically grow by default. Before 12c, Oracle can only achieve this function through sequence. In addition, we can also see that in this case, null values cannot be inserted.

SQL> alter table test modify (id default null );
Alter table test modify (id default null)
                          *
ERROR at line 1:
ORA-30674: identity column cannot have a default value

So for the column of identity, is it true that the null value cannot be inserted? Actually, it works, but you have to do this:

<Pre class = "brush: php"> SQL> create table test1 (id number generated by default on NULL as identity, name varchar2 (20 ));
 
Table created.
 
SQL>
SQL> insert into test1 values (1, '111cn. Net ');
 
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 111cn.net
2 baidu.com
1 google.com </pre>

However, we can see that, when the id column of the inserted 3rd pieces of data is null, oracle's actual value is 1. How does this 1 come from? The answer will be provided later.

SQL> update test1 set id = 100 where id = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test1;
 
ID NAME
------------------------------
1 111cn.net
100 baidu.com
1 google.com

In this case, you can perform a normal dml. Because identity column is null by default, the following Test continues.

SQL> create table test2 (id number generated always as identity, name varchar2 (20 ));
 
Table created.
 
SQL> insert into test2 (name) values ('111cn. Net ');
 
1 row created.
 
SQL> insert into test2 (name) values ('Baidu. Com ');
 
1 row created.
 
SQL> insert into test2 (name) values ('google. Com ');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test2;
 
ID NAME
------------------------------
1 111cn.net
2 baidu.com
3 google.com
SQL> update test2 set id = 4 where id = 2;
Update test2 set id = 4 where id = 2
                  *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
 
SQL> update test2 set id = 1 where id = 2;
Update test2 set id = 1 where id = 2
                 *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
 
SQL> delete from test2 where id = 2;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test2;
 
ID NAME
------------------------------
1 111cn.net
3 google.com

We can see that the update is not allowed. Here we should know that Oracle won't allow you to perform the update.

SQL>
SQL> select TABLE_NAME,
2 COLUMN_NAME,
3 DEFAULT_ON_NULL,
4 IDENTITY_COLUMN,
5 DATA_DEFAULT
6 from user_tab_columns
7 where IDENTITY_COLUMN = 'yes ';
 
TABLE_NAME COLUMN_NAME def ide DATA_DEFAULT
----------------------------------------------------------------------------------------------------------
Test id no yes "ROGER". "ISEQ $ _ 91820". nextval
TEST1 id yes "ROGER". "ISEQ $ _ 91822". nextval
TEST2 id no yes "ROGER". "ISEQ $ _ 91824". nextval
 
SQL>
 
SQL> set pagesize 200 long 9999
SQL> select dbms_metadata.get_ddl ('table', 'test') from dual;
 
DBMS_METADATA.GET_DDL ('table', 'test ')
--------------------------------------------------------------------------------
 
Create table "ROGER". "TEST"
("ID" number generated by default as identity minvalue 1 MAXVALUE 9999999999
999999999999999999 increment by 1 start with 1 CACHE 20 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"
 
SQL> select dbms_metadata.get_ddl ('table', 'test1') from dual;
 
DBMS_METADATA.GET_DDL ('table', 'test1 ')
--------------------------------------------------------------------------------
 
Create table "ROGER". "TEST1"
("ID" number generated by default on null as identity minvalue 1 MAXVALUE 99
99999999999999999999999999 increment by 1 start with 1 CACHE 20 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, we can see from the table definition that the ID column is defined as sequence by default. The attribute is similar to the attribute created in our previous version. By default, start with is 1. This is why id = 1 is inserted with null.