ThatJeffSmith

Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler

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