Defining 12c IDENTITY Columns in Oracle SQL Developer Data Modeler

thatjeffsmith SQL Developer 10 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 🙂

Tell Others About This Story:

Comments 10

  1. 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!

    1. thatjeffsmith Post
      Author
      1. 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;
        /

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

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

    1. thatjeffsmith Post
      Author

Leave a Reply

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