Columnas IDENTITY Oracle 12

Hola a todos, hoy queríamos comentar con vosotros una funcionalidad de Oracle 12.1 que es muy potente y se suele utilizar poco, las columnas IDENTITY.

Esta funcionalidad está implementada en la mayoría de los motores de bases de datos y por fin la tenemos disponible en Oracle. Sería similar a la columna AUTO_INCREMENT de MySQL o la columna IDENTITY de SQL Server. Su función es la de insertar un valor secuencial en una columna cada vez que se inserta un fila en una tabla, se suele utilizar para generar claves primarias. Su funcionalidad es muy similar a la utilización de secuencias pero su uso es más simple.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

Para su utilización son necesarios los permisos de create sequence y la parte de identity_options es muy similar a la de las secuencias, por lo que nos centraremos en la primera parte.

Si utilizamos ALWAYS forzamos la utilización de la columna IDENTITY. Si en el INSERT utilizamos un valor para esa columna o NULL obtendremos un error:

CREATE TABLE identity_tabla (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  descripcion VARCHAR2(30)
);

SQL> INSERT INTO identity_tabla (descripcion) VALUES ('Solo descripcion');

1 row created.

SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (NULL, 'ID=NULL y descripcion');
INSERT INTO identity_tabla (id, descripcion) VALUES (NULL, 'ID=NULL y descripcion')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (999, 'ID=999 y descripcion');
INSERT INTO identity_tabla (id, descripcion) VALUES (999, 'ID=999 y descripcion')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Con la utilización de BY DEFAULT podemos utilizar la columna IDENTITY en el INSERT y añadir valores manualmente. Si el valor insertado es NULL nos devolverá un error:

DROP TABLE identity_tabla PURGE;

CREATE TABLE identity_tabla (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  descripcion VARCHAR2(30)
);

SQL> INSERT INTO identity_tabla (descripcion) VALUES ('Solo descripcion');

1 row created.

SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (999, 'ID=999 y descripcion');

1 row created.

SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (NULL, 'ID=NULL y descripcion');
INSERT INTO identity_tabla (id, descripcion) VALUES (NULL, 'ID=NULL y descripcion')
                                                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."identity_tabla"."ID")

Con la utilización de BY DEFAULT ON NULL nos permite también la utilización de NULL en los INSERTs, aunque es ignorado:

DROP TABLE identity_tabla PURGE;

CREATE TABLE identity_tabla (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  descripcion VARCHAR2(30)
);

SQL> INSERT INTO identity_tabla (descripcion) VALUES ('Solo descripcion');

1 row created.

SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (999, 'ID=999 y descripcion');

1 row created.

SQL> INSERT INTO identity_tabla (id, descripcion) VALUES (NULL, 'ID=NULL y descripcion');

1 row created.

SQL> SELECT * FROM identity_tabla;

ID         descripcion
---------- ------------------------------
         1 Solo descripcion
       999 ID=999 y descripcion
         2 ID=NULL y descripcion

Cuando consultamos las vistas de sistemas parece que estamos utilizando una secuencia normal:

COLUMN object_name FORMAT A20

SELECT object_name, object_type
FROM   user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_92117         SEQUENCE
identity_tabla       TABLE

2 rows selected.

Para ver la información sobre las columnas IDENTITY tenemos que utilizar las vistas [DBA|ALL|USER]_TAB_IDENTITY_COLS:

SET LINESIZE 100
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A15
COLUMN generation_type FORMAT A10
COLUMN identity_options FORMAT A50

SELECT table_name, 
       column_name,
       generation_type,
       identity_options
FROM   all_tab_identity_cols
WHERE  owner = 'TEST'
ORDER BY 1, 2;
Columnas IDENTITY Oracle 12

Restricciones de las columnas IDENTITY

La utilización de columnas IDENTITY en Oracle tiene las siguientes restricciones:

  • Solo puede haber una columna IDENTITY por tabla.
  • Las columnas IDENTITY tienen que ser de tipo numérico, no se permiten tipos definidos por el usuario.
  • Las columnas IDENTITY no pueden tener clausula DEFAULT.
  • Cuando utilizamos CREATE TABLE … AS SELECT la columna de la nueva tabla no será IDENTITY. Si queremos mantener la misma estructura tenemos que crear la tabla primero y luego realizar un INSERT AS SELECT.

Esperamos que os sea de utilidad la entrada y que podáis utilizar esta nueva funcionalidad. Si quieres que analicemos tu caso para ver si es viable utilizar columnas IDENTITY. Contáctanos sin compromiso.

Nos vemos en próximas entradas.

Más info en: https://www.oracle.com/lad/technical-resources/articles/sql/oracle-db-12c-columna-identity.html

Comments are closed.