Identity Oracle 12cR2 column

User_OX9Q3

I am working in Oracle version 12cR2 and in SQL Server 2014, I have two problems with IDENTITY columns because I want to have the same behavior in both managers, the first problem is that in SQL Server when I insert a record sending a value for the IDENTITY column, for example 20, and in my next insert I do not send a value for the column I get a 21 record, in Oracle in the first record if I get 20 but when I perform the second insert I get record 1, the way to create the IDENTITY columns is as follows:

SQL server:
    ID INT IDENTITY (1,1) NOT NULL

ORACLE:
    ID NUMBER (10) GENERATED BY DEFAULT AS IDENTITY

In both managers the ID field is the primary key, the second case of error in Oracle is the following: when inserting a record without sending a value for the IDENTITY column I get record 1, if in my next insert I sent the value, for this example 2, if it is inserted, but when performing a third insert without sending a value for the column I get the error:

ORA-00001: single restriction violated

If I try to register again, it is already inserted without problem, to what I understand is that the SG has the number it goes in cached and always advances by 1 when an insert attempt is made.

Is there any external configuration to avoid these two problems in oracle? I hope you can help me, thanks

Paulzip

Oracle creates an internal sequence for identities behind the scenes and that sequence will follow its normally increment. So if you chose to specify your own value for the ID, it'll still use the next sequence value when you don't. It won't advance beyond the ID you specified, and I'd argue, nor should it.

If you want to advance an identity you can issue :

alter table your_table modify id
    generated by default on null as identity (start with limit value);

That'll do the same as SQL Server is doing.

The alternative (old approach) is to not bother with an identity and always issue an ID off a sequence rather than having an identity. You could have a trigger which populates from that if someone forgets

create or replace trigger TBI_MY_TABLE before insert on MY_TABLE for each row
begin
  if :new.ID is null then
    :new.ID := S_MY_SEQ.NextVal;
  end if;
end;

mathguy

I confess I don't understand the business case for "mixed" ID's.

If your data has an attribute ID, but that attribute doesn't always have a value, then it's not a good candidate for primary key. In that case, you can have a column ID in your table (even with a UNIQUE constraint on it), but don't use it as PK. If you need a PK column (you almost always do), create a separate column, GENERATED ALWAYS AS IDENTITY.

What you seem to want to do is to interpret your existing ID as a "partial PK" - some of the ID's come pre-populated, and they are unique, but some come as NULL and you want to assign unique values to them. I fail to see a good business case for this approach.

If you really need to do what you described (which I doubt very strongly), you could start the sequence (for the identity used as DEFAULT) at a high enough value. For example, if all your ID's are at most 18 digits, start the identity sequence at POWER(10, 18). Sequence values can be up to 28 digits, so there will still be plenty of room.

You may say "but the ID's must be consecutive numbers". That doesn't make sense in any case, and even less so in your problem; if the incoming, populated ID's have values 20, 55 and 320, you won't have "sequential" (consecutive) ID's until you insert at least 317 more rows with unassigned ID (so the ID will be generated automatically).