Nuove colonne Caratteristica IDENTITY

Nuove colonne Caratteristica IDENTITY
  • 17
    Feb

Nuove colonne Caratteristica IDENTITY

twittergoogle_pluslinkedin

In Oracle 12c quando creiamo una tabella si può riempire una colonna automaticamente tramite una sequenza generata
sistema utilizzando il generati come clausola di IDENTITY nella CREATE TABLE.

Possiamo usare GENERATED AS IDENTITY con il SEMPRE, predefinito o su parole chiave NULL e che influenzerà il modo in cui il valore della colonna di identità viene popolata.

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

Quando si specifica la parola sempre, non è possibile includere in modo esplicito i valori per la colonna Identity in istruzioni SQL INSERT o UPDATE.

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.

Non possiamo entrare in modo esplicito un valore per la colonna di identità come EMP_ID che viene generato automaticamente.

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

Vediamo un altro esempio utilizzando questa volta la parola chiave DEFAULT

SQL> drop table emp;

Table dropped.

SQL> create table emp
2 (emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY, ename varchar2(10));

Table created.

A differenza del caso precedente, possiamo specificare un valore per la colonna di identità. La colonna di identità viene popolata automaticamente solo se non forniamo un valore per la colonna di identità.

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 clausola assicura che inizialmente la colonna di identità sarà popolata automaticamente solo se nessun valore è fornito per la colonna e anche se un valore nullo viene fornito a differenza dell’esempio precedente

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

La sequenza avrà il prefisso ISEQ $$ seguito dalla ID oggetto della tabella.

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
————————————————————————————————————————
ISEQ$$_93421

C’è una nuova vista chiamata * _TAB_IDENTITY_COLS e la vista * _TABLES ha un nuovo HAS_IDENTITY rubrica

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