Home » SQL » Oracle 12c Identity Column Example

Oracle 12c Identity Column Example

By

Identity column feature is introduced in Oracle 12c. An identity column value is specified using a sequence generation statement.  In this tutorial, you will learn how to create a table with an identity column and how to add an identity column in an existing table.

Example - Create a New Table with Identity Column

The following example creates a table named TEST_IDCOLUMN and specifies that the ID column's default value is set to the next value from a sequence generation statement:

CREATE TABLE test_idcolumn (
student_no integer CONSTRAINT pk_test_idcolumn PRIMARY KEY,
student_name varchar2(100) NOT NULL,
id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 ORDER) 
)
/

Test

INSERT INTO TEST_IDCOLUMN (STUDENT_NO, STUDENT_NAME)
   VALUES ('109820', 'JOHN');
INSERT INTO TEST_IDCOLUMN (STUDENT_NO, STUDENT_NAME)
   VALUES ('109899', 'SMITH');

SELECT * FROM TEST_IDCOLUMN;

Output

STUDENT_NO STUDENT_NAME ID
109820 JOHN 1
109899 SMITH 2

You can see that we have inserted the values into STUDENT_NO and STUDENT_NAME column, and the value for the ID column is generated automatically.

Example - Add Identity Column to an Existing Table in Oracle 12c

The following ALTER TABLE statement will add the identity column in the table EMP. And the good part is that the sequence value will be generated for all the existing rows:

ALTER TABLE emp ADD id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 1);

See also: