Oracle 12c New Feature – Identity Columns 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
Posted on: Sun, 03 Nov 2013 07:21:31 +0000