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 🙂

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

11 Comments

  1. I would never have figured that out on myself. Thanks for clarifiying!

  2. Hello Jeff,

    I have a table with IDENTITY column and I also need another sequence in another column. For that second column under Auto Increment I have names entered for Sequence Name and Trigger Name. Generate Trigger is checked. When I generate the DDL I see the Identity column, and then towards the bottom of the DDL there is a create trigger statement , but sequence does not get generated. Could you check.

    Thanks!

    • Not w/o more information, like, what version? And show me the DDL that is generated please.

    • julesdba

      Jeff, thanks. Here is more info. However, this interestingly works with this small example, i.e. there is some other issue with my actual table.

      DM 4.2/932

      1. DDL with a surrogate column set as an identity column in properties. No identity clause set. There is also a second column with a sequence.

      CREATE TABLE test_schema.test_js (
      column1_sequence INTEGER,
      column2 VARCHAR2(255 CHAR),
      test_js_sid NUMBER NOT NULL
      )
      TABLESPACE users LOGGING;

      ALTER TABLE test_schema.test_js ADD CONSTRAINT test_js_spk PRIMARY KEY ( test_js_sid );

      CREATE SEQUENCE test_schema.test_seq START WITH 1 NOCACHE ORDER;

      CREATE OR REPLACE TRIGGER test_schema.test_trg BEFORE
      INSERT ON test_schema.test_js
      FOR EACH ROW
      WHEN (
      new.column1_sequence IS NULL
      )
      BEGIN
      :new.column1_sequence := test_schema.test_seq.nextval;
      END;
      /

      CREATE SEQUENCE test_schema.test_js_test_js_sid_seq START WITH 1 NOCACHE ORDER;

      ===========================

      2. DDL with IDENTITY clause

      CREATE TABLE test_schema.test_js (
      column1_sequence INTEGER,
      column2 VARCHAR2(255 CHAR),
      test_js_sid NUMBER
      GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH 1 NOCACHE ORDER )
      NOT NULL
      )
      TABLESPACE users LOGGING;

      ALTER TABLE test_schema.test_js ADD CONSTRAINT test_js_spk PRIMARY KEY ( test_js_sid );

      CREATE SEQUENCE test_schema.test_seq START WITH 1 NOCACHE ORDER;

      CREATE OR REPLACE TRIGGER test_schema.test_trg BEFORE
      INSERT ON test_schema.test_js
      FOR EACH ROW
      WHEN (
      new.column1_sequence IS NULL
      )
      BEGIN
      :new.column1_sequence := test_schema.test_seq.nextval;
      END;
      /

  3. 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.

    ALTER TABLE DROP CONSTRAINT ;

    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
    dropped.
    *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
    DROP IDENTITY.

  4. Good tutorial thanks! However my “Auto-increment DDL” field is disabled. Any idea why?

    • Ok forget it, I forgot to set auto-increment for field.

  5. Stefan Raabe Reply

    Hello Jeff,

    thanks for the good instructions on how to “data model” identity columns.
    Is it somehow possible to generate the identity column as
    “GENERATED BY DEFAULT ON NULL AS IDENTITY”

    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
    Stefan

Reply To michaelfromswitzerland Cancel Reply