How to: Manage Auto-Incremental Columns in Oracle

The purpose of this topic is to illustrate how to manage auto-incremental columns in Oracle with Telerik Data Access. The autoinc mechanism in Oracle is based on sequences and triggers. If you have a table in Oracle database whose primary key column is filled by a unique Oracle sequence, Telerik Data Access can automatically detect that the table has a server side generated primary key mechanism.

Database First Scenario

Suppose, you have the following Oracle table, sequence and trigger.

The table is named TASK and contains three columns.

The sequence is used to populate automatically the primary key column (the ID column). For example:

CREATE SEQUENCE   "TASK_SEQ"  
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE

And you have a before insert trigger that set the value of the ID column by using the created Oracle sequence. For example:

CREATE OR REPLACE TRIGGER  "BI_TASK"
 before INSERT ON "TASK"               
 FOR each ROW  
BEGIN   
   SELECT "TASK_SEQ".nextval INTO :NEW.ID FROM dual;
end;

When you create a new model, you need to specify in the configuration of the ID property that it is an identity property and uses the Autoinc key generator. This way, each time when you insert a new object runtime, Telerik Data Access will use the generated ID value to maintain the object identity. The ID column will be omitted from the generated insert statement and the value that has been generated by the sequence will be obtained.

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0);

taskConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc).
    HasFieldName("_ID").WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("ID").IsNotNullable().HasColumnType("INTEGER").HasPrecision(0).
    HasScale(0)

Telerik Data Access looks only for Before Insert triggers. All other types of triggers (After Insert, Before Update, After Update, Before Delete and After Delete) will be ignored, i.e. the Identity Mechanism will not be set to DatabaseServerCalculated and the Id column will be included in the generated insert statement).

Model First Scenario

If your entity has an integral (int, long, byte, short, uint, ulong, ubyte, ushort), double or float identity and the Identity Mechanism is set to DatabaseServerCalculated, the generated DDL script will add all necessary elements in the database schema to have a working autoinc setup.

The generated sequence and trigger will have the following name format: "Seq_TableName_ColumnName" and "Trg_TableName_ColumnName".

For example, if your entity is named TASK and the primary key property is named ID, the generated trigger and sequence will be:

CREATE SEQUENCE "Seq_TASK_ID"  START WITH 1  INCREMENT BY 1  NOMAXVALUE  NOCACHE
CREATE TRIGGER "Trg_TASK_ID"  
BEFORE INSERT ON "TASK"  
FOR EACH ROW   
BEGIN  
SELECT "Seq_TASK_ID".nextval INTO :NEW."ID" FROM dual;  
END ;;

If you change the configuration of the identity property from to the default one (no parameters are passed to the IsIdentity method) and migrate your database, Telerik Data Access will generate DDL for dropping the trigger only. Respectively, if you modify the identity mechanism of an entity from Default to calculated by the database server and migrate your database, Telerik Data Access will generate a trigger and a default sequence. If a sequence with the same name already exists, Telerik Data Access will use that sequence in the trigger and will not attempt to create a new one.

How to create/migrate the database based on your model is described in this article.