Oracle 12c New Feature IDENTITY Columns
- Posted by Gavin Soorma
- On November 12, 2014
- 0 Comments
In Oracle 12c when we create a table we can populate a column automatically via a system generated sequence by using the GENERATED AS IDENTITY clause in the CREATE TABLE statement.
We can use GENERATED AS IDENTITY with the ALWAYS, DEFAULT or DEFAULT ON NULL keywords and that will affect the way or when the identity column value is populated.
By default the GENERATED AS IDENTITY clause implicitly includes the ALWAYS keyword i.e GENERATED ALWAYS AS IDENTITY.
When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE SQL statements.
SQL> create table emp 2 (emp_id NUMBER GENERATED ALWAYS AS IDENTITY, ename varchar2(10)); Table created. SQL> desc emp Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- EMP_ID NOT NULL NUMBER ENAME VARCHAR2(10) SQL> alter table emp 2 add constraint pk_emp primary key (emp_id); Table altered. We cannot explicitly enter a value for the identity column EMP_ID as that is generated automatically. SQL> insert into emp 2 values 3 (1,'Bob'); insert into emp * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column SQL> insert into emp (ename) 2 values 3 ('Bob'); 1 row created. SQL> select * from emp; EMP_ID ENAME ---------- ---------- 1 Bob Let us look at another example using this time the DEFAULT keyword SQL> drop table emp; Table dropped. SQL> create table emp 2 (emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY, ename varchar2(10)); Table created. Unlike the previous case we can specify a value for the identity column. The identity column is only automatically populated if we do not provide a value for the identity column. SQL> insert into emp 2 values 3 (1,'Bob'); 1 row created. SQL> insert into emp 2 (ename) 3 values 4 ('Tom'); 1 row created. SQL> select * from emp; EMP_ID ENAME ---------- ---------- 1 Bob 2 Tom SQL> insert into emp 2 (ename) 3 values 4 ('Fred'); 1 row created. SQL> select * from emp; EMP_ID ENAME ---------- ---------- 1 Bob 2 Tom 3 Fred SQL> insert into emp 2 values 3 (4,'Jim'); 1 row created. SQL> insert into emp 2 (ename) 3 values 4 ('Fred'); insert into emp * ERROR at line 1: ORA-00001: unique constraint (SH.PK_EMP) violated - WHY??? SQL> insert into emp 2 (ename) 3 values 4 ('Tony'); 1 row created. SQL> select * from emp; EMP_ID ENAME ---------- ---------- 1 Bob 2 Tom 3 Fred 4 Tony Try and insert a null value SQL> insert into emp 2 values 3 (null,'Jim'); (null,'Jim') * ERROR at line 3: ORA-01400: cannot insert NULL into ("SH"."EMP"."EMP_ID”) BY DEFAULT ON NULL clause ensures that initially the identity column will only be populated automatically if no value is supplied for the column and also if a null value is provided unlike the previous example SQL> drop table emp; Table dropped. SQL> create table emp 2 (emp_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, ename varchar2(10)); Table created. SQL> insert into emp 2 (ename) 3 values 4 ('Tom'); 1 row created. SQL> insert into emp 2 values 3 (null,'Bob'); 1 row created. SQL> select * from emp; EMP_ID ENAME ---------- ---------- 1 Tom 2 Bob The sequence will have the prefix ISEQ$$ followed by the Object ID of the table. SQL> select sequence_name from user_sequences; SEQUENCE_NAME ------------------------------------------------------------------------------------------------------------------------ ISEQ$$_93421 There is a new view called *_TAB_IDENTITY_COLS and the *_TABLES view has a new column HAS_IDENTITY SQL> select table_name, column_name, generation_type,identity_options 2 from user_tab_identity_cols where sequence_name='ISEQ$$_93421'; TABLE_NAME ------------------------------------------------------------------------------------------------------------------------ COLUMN_NAME ------------------------------------------------------------------------------------------------------------------------ GENERATION ---------- IDENTITY_OPTIONS ------------------------------------------------------------------------------------------------------------------------ EMP EMP_ID BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR DER_FLAG: N SQL> select has_identity from user_tables where table_name='EMP'; HAS --- YES
0 Comments