I am trying the new Oracle 12c and its feature to create columns with the keyword IDENTITY.
I create a table
CREATE TABLE xt (a NUMBER GENERATED by default AS IDENTITY PRIMARY KEY, b VARCHAR2(10));
And populate it, sometimes specifying the value for a and sometimes relying on the system to generate the value:
INSERT INTO xt (b) values ('a');
INSERT INTO xt (b, a) values ('b', default);
INSERT INTO xt (b, a) values ('c', 3);
INSERT INTO xt (b) values ('d');
INSERT INTO xt (b) values ('e');
The problem is that the fourth INSERT fails because the system tries to use the value 3 which is already taken. The fifth statement gets the value 4. And the table now contains:
A B
- -
1 a
2 b
3 c
4 e
Is there something I am missing? I understood that by specifying BY DEFAULT, I would be allowed to sometimes specify values on my own without them interfering with the generated values. I now that the same thing works correctly in MySQL (where I would get five rows from 1 to 5 with the same INSERT statements)
/nikos