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);