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.
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.
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.
Ok, now preview the DDL
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 🙂
11 Comments
I would never have figured that out on myself. Thanks for clarifiying!
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.
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;
/
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.
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.
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
Thank you for sharing that. It helps so much.
People like you, inspire. Thanks
Raul, thank you SO MUCH for sharing that. Much appreciated.