ORA-01758 When Adding IDENTITY Column Creates Anyway A System Generated Sequence (Doc ID 1960560.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

On 12.1.0.2 database version.

When attempting to add IDENTITY column to a table with data, the following error occurs and the sequence is created anyway.

ORA-01758: table must be empty to add mandatory (NOT NULL) column



Steps to reproduce:

The issue can be reproduced at will with the following steps:
1. create a table
2. populate the table with data
3. add the identity primary key column

Test Case:


Due to this issue, users cannot create an identity columns on existing tables with data in it.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms