In an effort to simplify the migration process from other RDBMS sources to Oracle databases, Oracle introduced in version 12c a new feature called “Identity Columns”.

Based on a sequence generator, the identity column will be assigned an increasing integer value from this sequence generator. Identity columns are automatically incrementing columns and can be indexed as any other column type.

You can use the identity_options clause to configure the sequence generator with the same parameters as the CREATE SEQUENCE statement. The identity_options clause has two possible values: “ALWAYS” and “BY DEFAULT”.

The “ALWAYS” option forces the database to always uses the sequence generator to assign a value to the column as default behavior. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned.

When “BY DEFAULT” option is specified during table creation, then the database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column.

Let’s go through some examples, but first, let’s create a database user account:

SQL> CREATE USER z_identity
  2    IDENTIFIED BY nueva123
  3    DEFAULT TABLESPACE users
  4    QUOTA UNLIMITED ON users;

User created.

SQL> GRANT RESOURCE TO z_identity;

Grant succeeded.

SQL> GRANT CREATE SESSION TO z_identity;

Grant succeeded.

Using the “ALWAYS” option

Remember: in this mode, the column cannot be updated by the users. The default value given by sequence generator is the only possible value for the column and if you try to insert a value into the column then an error will be raised.

SQL> CONN z_identity/nueva123;
Connected.
SQL> CREATE TABLE test_always  (
  2    ident      NUMBER GENERATED ALWAYS AS IDENTITY,
  3    description VARCHAR2(100));

Table created.

Let’s describe the table, it looks like any other table:

SQL> desc test_always
 Name                           Null?    Type
 ------------------------------ -------- ----------------------------
 IDENT                          NOT NULL NUMBER
 DESCRIPTION                             VARCHAR2(100)

If you want to insert a value into IDENT column an error will be raised:

SQL> INSERT INTO test_always VALUES (1, 'record#1');
INSERT INTO test_always VALUES (1, 'record#1')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

To avoid this problem, just insert values for DESCRIPTION column only as follows

SQL> INSERT INTO test_always (description) VALUES ('record#1');

1 row created.

SQL> INSERT INTO test_always (description) VALUES ('record#2');

1 row created.

SQL> INSERT INTO test_always (description) VALUES ('record#3');

1 row created.

SQL> SELECT * FROM test_always;

     IDENT DESCRIPTION                                                                     
---------- ---------------------------------------------------------------                                                                     
         1 record#1                                                                        
         2 record#2
         3 record#3

Using the “By Default” option

Remember: In this case, you can also explicitly assign a specified value to the column.

SQL> CREATE TABLE test_by_default (
  2    ident      NUMBER GENERATED BY DEFAULT AS IDENTITY,
  3    description VARCHAR2(100));

Table created.

Let’s see how it works:

SQL> INSERT INTO test_by_default (description) VALUES ('record #1');

1 row created.

SQL> INSERT INTO test_by_default VALUES (5,'record #2');

1 row created.

SQL> INSERT INTO test_by_default (description) VALUES ('record #3');

1 row created.

SQL> SELECT * FROM test_by_default;

     IDENT DESCRIPTION                                                                     
---------- ---------------------------------------------------------------                                                                     
         1 record #1                                                                        
         5 record #2
         2 record #3

Notice how “record #2″ has “5” as IDENT value and how the sequence generator worked in “record #1″ and “record #3″ cases incrementing values automatically.

Configuring the sequence generator

For both “ALWAYS” and “BY DEFAULT” options you can configure the sequence generator as follows:

SQL> CREATE TABLE test_sequence_generator (
  2    ident      NUMBER GENERATED BY DEFAULT AS IDENTITY
  3             (START WITH 10 INCREMENT BY 12),
  4    description VARCHAR2(100));

Table created.

And now populate some data to see how it works:

SQL> INSERT INTO test_sequence_generator (description) VALUES ('record #1');

1 row created.

SQL> INSERT INTO test_sequence_generator (description) VALUES ('record #2');

1 row created.

SQL> INSERT INTO test_sequence_generator (description) VALUES ('record #3');

1 row created.

SQL> SELECT * FROM test_sequence_generator;

     IDENT DESCRIPTION                                                                     
---------- ---------------------------------------------------------------                                                                     
        10 record #1                                                                        
        22 record #2
        34 record #3

Notice how IDENT column values were automatically increased by the specified increments.

Finally, you can list your user objects in order to verify that identity columns are based on sequences.

SQL> SELECT object_name, object_type
  2    FROM user_objects
  3   ORDER BY object_type;

OBJECT_NAME                OBJECT_TYPE
-------------------------- -----------------------                                                         
ISEQ$$_92418               SEQUENCE                                                                                                                                                                                                             
ISEQ$$_92420               SEQUENCE                                                     
ISEQ$$_92422               SEQUENCE                                                     
TEST_SEQUENCE_GENERATOR    TABLE                                                     
TEST_ALWAYS                TABLE                                                     
TEST_BY_DEFAULT            TABLE                                                    

6 rows selected.

Sequences for the identity columns were created by the database engine automatically.

Link to script that contains the examples in this post.

 

 

 

 

 

 

 

Comment