New Feature IDENTITY Columns

In Oracle 12c, quando creiamo una tabella, possiamo popolare una colonna automaticamente tramite una sequenza generata dal sistema utilizzando la clausola GENERATED AS ++ nell’istruzione CREATE TABLE.

Possiamo usare GENERATED AS IDENTITY con le parole chiave ALWAYS, DEFAULT o DEFAULT ON NULL e ciò influenzerà il modo o il momento in cui il valore della colonna Identity viene popolato.

Per impostazione predefinita, la clausola GENERATED AS IDENTITY include implicitamente la parola chiave ALWAYS, ovvero GENERATED ALWAYS AS IDENTITY.

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