• Home
  • About
  •  

    Oracle 12c – Identity Columns

    Oracle 12c New Feature – Identity Columns

    In a previous post I investigated a new Oracle 12c capability that allows the DEFAULT clause for a column to specify the NEXTVAL or CURRVAL pseudo-columns. This post discusses identity columns which can be specified in the CREATE TABLE and ALTER TABLE statements.

    This new feature allows you to specify that a column should be automatically populated from a system-created sequence.

    Creating Identity Columns

    First an example of a table without an identity clause:

    CREATE TABLE transaction1
    (
      transaction_id   NUMBER,
      holding_id       NUMBER,
      transaction_type NUMBER,
      transaction_date DATE,
      credit_value     NUMBER,
      debit_value     NUMBER
    );
    

    This definition creates the following table

    SQL> DESC transaction1
    
    Name                                      Null?    Type
    ----------------------------------------- -------- ------------------------
    TRANSACTION_ID                                     NUMBER
    HOLDING_ID                                         NUMBER
    TRANSACTION_TYPE                                   NUMBER
    TRANSACTION_DATE                                   DATE
    CREDIT_VALUE                                       NUMBER
    DEBIT_VALUE                                        NUMBER
    

    In its simplest form the IDENTITY clause can be specified as follows:

    CREATE TABLE transaction2
    (
      transaction_id   NUMBER GENERATED AS IDENTITY,
      holding_id       NUMBER,
      transaction_type NUMBER,
      transaction_date DATE,
      credit_value     NUMBER,
      debit_value     NUMBER
    );
    

    This definition creates the following table:

    SQL> DESC transaction2
    
    Name                                      Null?    Type
    ----------------------------------------- -------- ------------------------
    TRANSACTION_ID                            NOT NULL NUMBER
    HOLDING_ID                                         NUMBER
    TRANSACTION_TYPE                                   NUMBER
    TRANSACTION_DATE                                   DATE
    CREDIT_VALUE                                       NUMBER
    DEBIT_VALUE                                        NUMBER
    

    Note that a NOT NULL constraint has been automatically created for the TRANSACTION_ID column

    A sequence is created with a system-generated name in the format ISEQ$$_<objectID> where objectID is the object id of the table.

    In the above example, the OBJECT_ID of the TRANSACTION table is 92210 and the system-generated name of the sequence is ISEQ$$_92210.

    The automatically created sequence is visible in DBA_SEQUENCES. For example

    SELECT sequence_owner, sequence_name 
    FROM dba_sequences;
    
    SEQUENCE_OWNER  SEQUENCE_NAME
    --------------  -------------
              US01   ISEQ$$_92210
    

    Note that the sequence is owned by the table owner (US01), not the SYS user.

    By default the GENERATED AS IDENTITY clause implicitly includes the ALWAYS keyword i.e GENERATED ALWAYS AS IDENTITY. When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE statements. For example the following statement:

    INSERT INTO transaction2
    (transaction_id,holding_id,transaction_type,transaction_date,credit_value)
    VALUES
    (50,100,1,SYSDATE,300);
    

    will result in an error:

    ORA-32795: cannot insert into a generated always identity column
    

    Values can be generated automatically if no values are specified explicitly using the following syntax:

    GENERATED BY DEFAULT AS IDENTITY
    

    If you only wish values to be generated when a NULL value is supplied use

    GENERATED BY DEFAULT AS IDENTITY ON NULL
    

    More sophisticated syntax is available. For example (from the documentation):

    CREATE TABLE transaction3
    (
      transaction_id   NUMBER
        GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10),
      holding_id       NUMBER,
      transaction_type NUMBER,
      transaction_date DATE,
      credit_value     NUMBER,
      debit_value     NUMBER
    );
    

    In the above example, a starting value and increment value have been specified for the sequence. Interestingly the increment value stored in the INCREMENT$ column of the SEQ$ table in the data dictionary. However the starting value does not appear to be stored in this
    table. When the first row is inserted into the table, the TRANSACTION_ID column will be set to the value of 100. So it works, but I don’t fully understand why it works.

    We can check which tables have identity columns in the DBA_TABLES

    SELECT table_name,has_identity
    FROM dba_tables
    WHERE owner = 'US01'
    AND table_name LIKE 'TRANSACTION%';
    
    TABLE_NAME                     HAS_IDENTITY
    ------------------------------ ------------
    TRANSACTION1                             NO
    TRANSACTION2                            YES
    TRANSACTION3                            YES
    

    When the identity clause is specified, a couple of extra bits are set in the PROPERTY column of the TAB$ table.

    SELECT
      o.name AS name,
      TO_CHAR (t.property,'XXXXXXXXXXXXXXXXXX') AS property
    FROM sys.tab$ t,sys.obj$ o
    WHERE t.obj# = o.obj#
    AND o.owner# =
    (
      SELECT user# FROM sys.user$
      WHERE name = 'US01'
    )
    AND o.name LIKE 'TRANSACTION%';
    
    NAME          PROPERTY
    ------------  ---------------
    TRANSACTION1        420000000
    TRANSACTION2  400000420000000
    TRANSACTION3  400000420000000
    

    According to $ORACLE_HOME/rdbms/admin/dcore.bsq the flag values are:

    0x20000000 = pdml itl invariant
    0x400000000 = delayed segment creation
    0x400000000000000 = has identity column
    

    The delayed segment creation flag is unset when a segment is created for the table i.e. when the first row is inserted.

    The identity column property shown above is used to populate the HAS_IDENTITY column in the DBA_TABLES view.

    The PROPERTY column in SYS.COL$ is also set for the identity clause. The following query lists the value of the PROPERTY column in COL$ for the TRANSACTION_ID column in each of the three tables:

    SELECT
      o.name AS table_name,
      c.name AS column_name,
      TO_CHAR (c.property,'XXXXXXXXXXXXXXXXXX') AS property,
      c.default$ AS default_value
    FROM sys.col$ c,sys.tab$ t,sys.obj$ o
    WHERE c.obj# = t.obj#
    AND t.obj# = o.obj#
    AND o.owner# =
    (
      SELECT user# FROM sys.user$
      WHERE name = 'US01'
    )
    AND o.name LIKE 'TRANSACTION%'
    AND c.intcol# = 1;
    

    Results are:

    TABLE_NAME    COLUMN_NAME       PROPERTY  DEFAULT_VALUE                
    ----------    -------------   ----------  -----------------------------
    TRANSACTION1  TRANSACTION_ID           0
    TRANSACTION2  TRANSACTION_ID  2800000000  "US01"."ISEQ$$_92210".nextval
    TRANSACTION3  TRANSACTION_ID  4800000000  "US01"."ISEQ$$_92212".nextval
    

    According to $ORACLE_HOME/rdbms/admin/dcore.bsq the flag values are:

    0x002000000000 = generated always identity column
    0x004000000000 = generated by default identity col 
    

    Under the covers, therefore the identity clause uses the default column value functionality.

    Altering Identity Columns

    The ALTER TABLE statement supports the identity clause, but only to modify the attributes of EXISTING identity columns. In other words you cannot alter an existing non-identity column to become an identity column. If you try to do this the following error will be returned:

    ORA-30673: column to be modified is not an identity column
    

    In this example the table is created with an identity column. The BY DEFAULT ON NULL clause ensures that initially the identity column will only be populated automatically if no value is supplied for the column:

    CREATE TABLE transaction4
    (
      transaction_id   NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
      holding_id       NUMBER,
      transaction_type NUMBER,
      transaction_date DATE,
      credit_value     NUMBER,
      debit_value     NUMBER
    );
    

    Assume the following statements are executed. Note that a value for the TRANSACTION_ID column is specified explicitly.

    INSERT INTO transaction4 VALUES (1001,10,1,SYSDATE,200,0);
    INSERT INTO transaction4 VALUES (1002,11,2,SYSDATE,0,100);
    INSERT INTO transaction4 VALUES (1003,21,1,SYSDATE,0,200);
    INSERT INTO transaction4 VALUES (1004,22,1,SYSDATE,300,0);
    
    COMMIT;
    

    The highest value for this column can be verified using:

    SELECT MAX(transaction_id) FROM transaction4;
    
    MAX(TRANSACTION_ID)
    -------------------
                   1004
    

    If we now decide that we want this value to be populated automatically we can issue the following ALTER TABLE statement:

    ALTER TABLE transaction4 MODIFY transaction_id
    GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);
    

    The START WITH LIMIT VALUE clause can only be specified with an ALTER TABLE statement (and by implication against an existing identity column). When this clause is specified, the table will be scanned for the highest value in the TRANSACTION_ID column and the sequence will commence at this value + 1. In this case the highest value is 1004 so the next sequence number will be 1005.

    For example, we can now execute the following statements which do not specify a value for the TRANSACTION_ID column:

    INSERT INTO transaction4
    (holding_id,transaction_type,transaction_date,credit_value,debit_value)
    VALUES (31,1,SYSDATE,150,0);
    
    INSERT INTO transaction4
    (holding_id,transaction_type,transaction_date,credit_value,debit_value)
    VALUES (32,2,SYSDATE,0,250);
    
    COMMIT;
    

    The value for TRANSACTION_ID is generated automatically.

    We can verify the highest value for this column again:

    SELECT MAX(transaction_id) FROM transaction4;
    
    MAX(TRANSACTION_ID)
    -------------------
                   1006
    

    Dropping Identity Columns

    Whilst we cannot change a non-identity column to an identity column, we can convert an identity column to a non-identity column:

    For example if we create the following table:

    CREATE TABLE transaction5
    (
      transaction_id   NUMBER GENERATED AS IDENTITY,
      holding_id       NUMBER,
      transaction_type NUMBER,
      transaction_date DATE,
      credit_value     NUMBER,
      debit_value     NUMBER
    );
    
    SELECT has_identity
    FROM dba_tables
    WHERE owner = 'US01'
    AND table_name = 'TRANSACTION5';
    
    HAS_IDENTITY
    ------------
             YES
    

    We can use the ALTER TABLE statement to drop the identity column metadata, though not the column itself:

    ALTER TABLE t104 MODIFY transaction_id DROP IDENTITY;
    
    SELECT has_identity
    FROM dba_tables
    WHERE owner = 'US01'
    AND table_name = 'TRANSACTION5';
    
    HAS_IDENTITY
    ------------
              NO
    

    Restrictions

    As you would expect there are a few restrictions on the use of identity columns.

    • A table can only contain one identity column
    • The column must be a numeric data type
    • If an identity clause is specified for a column, then a default clause cannot be specified for that column
    • NOT NULL and NOT DEFERRABLE constraints are created automatically for the identity column if they do not already exist. Conflicting constraints will raise an error.
    • CREATE TABLE AS SELECT statements do not inherit the identity property of a column in the source table.

    Dropping Tables with Identity Columns

    One important point to note is that when the table is dropped; for example

    DROP TABLE t1;
    

    the system-generated sequence is not dropped. As the sequence name is based on the object number of the table, which cannot be reused, this is not particularly significant unless the table will be dropped and recreated on a regular basis.

    Differences between Identity Columns and Column Default Clauses

    There are some subtle differences between the identity clause and the new feature that allows a column default clause to include the NEXTVAL or CURRVAL pseudo-columns. I have called the latter feature implicit sequences though this is not the official name.

    With implicit sequences, the sequence must already exist and you must have the correct permissions to increment it; with the identity clause, the sequence is system-generated when the table is created or altered.

    When a table that uses implicit sequences is dropped and recreated, the sequence will be unaffected and the next insertion into the table will use the next value allocated from the sequence.

    When a table that uses the identity clause is dropped and recreated, the sequence will still be unaffected. However, a new sequence will be created when the table is recreated and insertions will use values from the new sequence.

    With implicit sequences, multiple tables can share a single sequence value. With the identity clause, each table has a dedicated sequence and therefore sharing is not possible.

    Implicit sequences can be introduced for existing tables; the identity clause cannot be used to alter an existing table unless the column on which it acts was originally created as an identity column.

    End of post

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    HTML tags are not allowed.