Issue With Identity And Autoincrement When Using Default Value in SQL Developer Data Modeler (Doc ID 2890970.1)

Last updated on AUGUST 25, 2022

Applies to:

Symptoms

Environment
-----------------------
SQL Data Modeler 22.2
Database 19.3.0.0.0
Windows 10

Problem
--------------
When using the default value for a column and using a sequence to set it and importing this table into Data Modeler, data modeler thinks it's an identity column and checks the autoincrement and identity for that columns properties. This doesn't really affect the DDL, however, it's confusing as it should be setting up the column as "IDENTITY" but it doesn't.

Steps to Reproduce
--------------------------
In SQL Developer 22.2 do the following:

1. Create user <username>
2. Grant all privileges to <username);

3. Run script
CREATE SEQUENCE scott_berry.BATCH_EXECUTION_BATCH_EXECUTION_ID_SEQ2 START WITH 1 NOCACHE ORDER;

4. Run script
CREATE TABLE <username>.BATCH_EXECUTION2 (
BATCH_EXECUTION_ID NUMBER DEFAULT <username>.BATCH_EXECUTION_BATCH_EXECUTION_ID_SEQ2.NEXTVAL
CONSTRAINT CK_BATCH_EXECUTION_BATCH_EXECUTION_ID_NN2 NOT NULL,
BATCH_EXECUTION_NAME VARCHAR2(100 BYTE),
START_TIME DATE,
END_TIME DATE
)
LOGGING;

5. Go to SQL Developer Data Modeler

6. Import table into Data Modeler using File->Import->Data Dictionary

7. Check the Batch_execution_id column by double clicking on the table and double clicking on that column and I can reproduce the customers issue as my Identity and Autoincrement checkboxes are checked.

8. In default and constraint section and the default value and it is blank.

Symptoms
------------------

1. Issue only happens when you use schema name of the sequence in the default value then it sets the autoincrement and identity check boxes.
2. During import in a table that has a default value from a sequence (Import from Data Dictionary), it will set the autoincrement and identity check boxes to checked for that column.
3. When try and fix this to uncheck the identity and autoincrement to unchecked, When you try to sync the data dictionary, it wants to remove the identity column property. (it thinks it has a column defined as Identity in the data dictionary)
4. If use the physical model for 21C and do a generate DDL, it will generate the Trigger unless you uncheck the autoincrement and identity.
5. If use the physical model for 12CR2 and do a generate DDL, it will not generate the Trigger if the autoincrement and identity boxes are checked.

In Addition
-----------------
Also, after unchecking the autoincrement and identity and adding the sequence.nextval to the default value and choosing to synch data dictionary, it generates this ddl which is already setup for #1 and #2 gets an error since the column isn't an identity column:

--1)
ALTER TABLE DW_VALIDATE.BATCH_EXECUTION2 MODIFY (
  BATCH_EXECUTION_ID DEFAULT ON NULL BATCH_EXECUTION_BATCH_EXECUTION_ID_SEQ.NEXTVAL
);

--2)
ALTER TABLE DW_VALIDATE.BATCH_EXECUTION2 MODIFY (
  BATCH_EXECUTION_ID DROP IDENTITY
);

Error
-------------
#2 -> Error starting at line : 49 in command -
ALTER TABLE DW_VALIDATE.BATCH_EXECUTION2 MODIFY (
BATCH_EXECUTION_ID DROP IDENTITY
)
Error report -
ORA-30673: column to be modified is not an identity column
30673.0000 - "column to be modified is not an identity column"
*Cause: An attempt was made to modify the identity properties of
  that is not an identity column.
*Action: Modify the identity properties of an identity column.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.