Wednesday, March 30, 2016

Finally Auto increment ID in Oracle provided by Oracle DB 12c

Those who worked with few databases have known that Oracle Database had a lack of functionality to create an ID column which was an incremental number to be used as ID. Now, it provided with the name "Identity Columns" in Oracle 12c version. Even, now we can use sequence.nextval in default part of columns which is not advisable (using Identity is a better option).
In other DBs it looks like:
in MySQL  : ID int NOT NULL AUTO_INCREMENT
in MS SQL : ID int IDENTITY(1,1) PRIMARY KEY
in Access    : ID Integer PRIMARY KEY AUTOINCREMENT
Previously in Oracle to have an ID column that starts with 1 and increments one by one for each new record we need to create a sequence and a trigger to assign a number from sequence to column for each insert query. However, with IDENTITY we can alter a column as auto increment identity. For example:
CREATE TABLE ATK_test_t
( id NUMBER GENERATED ALWAYS AS IDENTITY, name VARCHAR2(255) );
Now, by just an insert and without mentioning "id" column in the insert, it will get 1 for the first row and 2 for the second insert.
INSERT INTO ATK_test_t (name) VALUES ('First Name');
INSERT INTO ATK_test_t (name) VALUES ('Second Name'),(;Third Name');
and the result will be:
SQL> SELECT * FROM ATK_test_t; 
ID          NAME
---------- ------------------------------
1            First Name
2            Second Name
3            Third Name
This new Identity function has three types of:
  1. ALWAYS: It gets number, and you do not allow to assign any other number in insert or pass null to this column ( generate error ORA-32795) 
  2. DEFAULT: We can assign or update any number but cannot assign Null 
  3. DEFAULT ON NULL: We can assign Null, but Oracle will assign next number for its value.
  I have found this functionality here with good explanation and more examples.

2 comments:

  1. Hyvä artikkeli. Oli mielenkiintoista lukea. http://www.connect.fi/

    ReplyDelete
  2. This explanations are awesome! I want to say thank you for these tips, use dissertation. You will be glad!

    ReplyDelete