« Oracle 12c New Features : Último login en SqlPlusOracle 12c New Features : Restauración de tablas con RMAN »

Oracle 12c New Features : Columnas con autoincremento (IDENTITY)



En Oracle 12c, se generá un nuevo tipo de columna , conocida como IDENTITY, la gracia de estás columnas es que son autonumérica,o sea, van generando de forma automática una secuencia cada vez que le insertamos un registro.

He aquí un ejemplo básico

CREATE TABLE tabla1 (secuencia NUMBER GENERATED AS IDENTITY ,
fecha date);


Cuando se genera esta tabla , por defecto se genera una secuencia , la cual podemos ver con esta consulta

SELECT sequence_owner||'.'||sequence_name secuencia,
min_value,
max_value,
increment_by,
cycle_flag,
order_flag,
cache_size,
last_number
FROM dba_sequences
WHERE sequence_owner LIKE '%PROD1%';

SECUENCIA                              MIN_VALUE                         MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------------- ---------- --------------------------------- ------------ - - ---------- -----------
PROD1.ISEQ$$_92175                             1      9999999999999999999999999999            1 N N         20      1


Le asignamos un registro a la tabla

SQL> Insert into tabla1 (fecha) values (sysdate);

1 row created.


Con esto veremos como aumenta de forma automática la secuencia

SQL> select * from tabla1;

 SECUENCIA FECHA
---------- --------
         1 15/07/13


Si tratamos de insertar un valor en la columna que es de tipo IDENTITY, arrojará un error

SQL> Insert into tabla1 (secuencia, fecha) values (3,sysdate);
Insert into tabla1 (secuencia, fecha) values (3,sysdate)
*
ERROR at line 1:
ORA-32795: no se puede insertar una columna de identidad siempre generada


Podemos cambiar la definición de la tabla , para así de esta forma poder insertar datos en el campo sin que nos de un error por la autosecuencia

Creamos la tabla

1 CREATE TABLE tabla3 (secuencia NUMBER GENERATED BY DEFAULT AS IDENTITY ,
2* fecha date)
SQL> /

Table created.


Insertamos datos para que se genere la autosecuencia

SQL> insert into tabla3 (fecha) values (sysdate);

1 row created.

SQL> select * from tabla3;

SECUENCIA FECHA
---------- --------
1 15/07/13


Ahora le insertamos datos indicando el campo de forma explícita

SQL> insert into tabla3 (secuencia, fecha) values (2,sysdate);

1 row created.


Y le insertamos datos para que genere la autosecuencia

SQL> insert into tabla3 (fecha) values (sysdate);

1 row created.


Al momento de consultar la tabla, nos podemos dar cuenta que no hubo error, pero se repitio el valor de la secuencia

SQL> select * from tabla3;

 SECUENCIA FECHA
---------- --------
         1 15/07/13
         2 15/07/13
         2 15/07/13


Si el campo que es de tipo IDENTITY le agregamos una PK , el ideal es que el campo IDENTITY no sea creado con BY DEFAULT, pues esto puede provocar que haya una violación de constraints

SQL> alter table tabla3 add constraint tabla3_pk primary key (secuencia);

Table altered.

SQL> insert into tabla3 values (5,sysdate);

1 row created.

SQL> select * from tabla3;

 SECUENCIA FECHA
---------- --------
         5 15/07/13

SQL> insert into tabla3 (fecha) values (sysdate);

1 row created.

SQL> select * from tabla3;

 SECUENCIA FECHA
---------- --------
         5 15/07/13
         3 15/07/13

SQL> insert into tabla3 (fecha) values (sysdate);

1 row created.

SQL> select * from tabla3;

 SECUENCIA FECHA
---------- --------
         4 15/07/13
         5 15/07/13
         3 15/07/13

** Aca se iba a generar el número 5 a través del campo IDENTITY , pero no lo hará dado que se produce una violación de constraints
SQL> insert into tabla3 (fecha) values (sysdate);
insert into tabla3 (fecha) values (sysdate)
*
ERROR at line 1:
ORA-00001: restricción única (PROD1.TABLA3_PK) violada

** Adelantamos la secuencia en forma manual

SQL> select PROD1.ISEQ$$_92179.nextval from dual;

   NEXTVAL 
----------
         6

** Y ahora sí se puede activar la creación del campo mediante el uso de la secuencia
SQL> insert into tabla3 (fecha) values (sysdate);

1 row created.

SQL> select * from tabla3;

 SECUENCIA FECHA
---------- --------
         4 15/07/13
         7 15/07/13
         5 15/07/13
         3 15/07/13


Se debe tener en cuenta que la tabla al tener una columna IDENTITY tiene por debajo una secuencia trabajando, al tener una secuencia trabajando se puede crear la columna IDENTITY tal cual se crea una secuencia, o sea, podría tener una columna que con cada inserción avance de 10 en 10 , que sea un valor cíclico con máximo de 1000 por ejemplo.

CREATE TABLE tabla2 (secuencia NUMBER GENERATED AS IDENTITY (START WITH 100 INCREMENT BY 50) , fecha date);

La anterior tabla tiene un campo IDENTITY generado , que primero inserta 100, después 150, 200,250, etc

Espero les sirva

by Ligarius
15.07.13. 14:11:21. 646 words, 4064 views. Categories: Oracle 12c ,