Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler

thatjeffsmith SQL Developer 7 Comments

Tell Others About This Story:

You’re excited to see that defining triggers and sequences to populate identity columns in Oracle Database is no longer required. You have an Oracle Database 12c instance up and running, and you’re ready to hit the ground running.

Wait, what is Jeff talking about?

Ok, let’s say I have a ‘BEER’ table. My Primary Key (PD) will be an integer. I want BREWERY #1 to have an ID of 1, and then each new BREWERY’s ID to increment by 1, and go on until we run out of numbers 😉 In 11gR2 and earlier database versions, I would have to create a sequence to handle the generation of the numbers and a trigger to fire to grab the nextval sequence and feed it to the column on an INSERT.

But now you have an option to do all that work in the definition of the table and the column itself.

You can read all about it here in the DOCS.

Now How Do I Draw That Up in SQL Developer Data Modeler?

Draw your table. You’ll want a column. Make sure it’s using a datatype that makes sense.

Relational Model, Column Properties

Relational Model, Column Properties

Ok, the Modeler now knows that this column is identifying, and that’s it’s going to be self-incrementing. Next we need to fill in the details. Remember all the flexibility you had before with defining how the sequence behaved? You still have that going for you in 12c. Just provide the information as before.

Min, max, increment by, cache?

Min, max, increment by, cache?

Just one more thing.

The modeler knows what you want to do with the column, but it doesn’t know what RDBMS features it has at its disposal. We need to go into the Physical Model level, ensuring we create a 12c physical model.

After you've created the 12c physical model, go to the table, column and access its properties

After you’ve created the 12c physical model, go to the table, column and access its properties

You want the one that says 'Identity' :)

You want the one that says ‘Identity’ 🙂

Ok, now preview the DDL

That looks right to me...

That looks right to me…

Way Fewer Objects to Create and Maintain

If your application has hundreds or even thousands of these fields, the amount of objects you DON’T have to create or maintain going forward is going to be huge. And if you’re migrating from say Sybase ASE or SQL Server, you don’t have to create ‘extra’ objects anymore. I’ll take less work anytime 🙂

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 7

  1. What is the best practice to update tables that have an identity column? I use Synchronize Data Dictionary. The generated script includes a statement to drop the not null constraint on the identity column. However, this results in the error below and I need to drop manually the IDENTITY first.


    Error report –
    ORA-30672: cannot drop NOT NULL constraint on an identity column
    30672.0000 – “cannot drop NOT NULL constraint on an identity column”
    *Cause: The NOT NULL constraint on an identity column could not be
    *Action: Do not drop the NOT NULL constraint on an identity column.
    The only way to drop the constraint is to remove the identity
    property of the column with ALTER TABLE MODIFY

  2. Hello Jeff,

    thanks for the good instructions on how to “data model” identity columns.
    Is it somehow possible to generate the identity column as

    With the column options Auto Increment -> generate “ALWAYS” and “WHEN NULL” I’m not able to get that result.

    Thanks for your feedback.

    Best regards

    1. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *