Oracle SQL | Auto incremented ID is not incremented when an id is provided

Question

I have following incrementing id:

create table PATIENT (
   PATIENTID            INTEGER             
      generated by default on null as identity ( start with 1 nocycle order)  not null
);

I noticed that, when I provide an id ( for example on my first inserts) the id in the created sequence does not increment.

As a result if I add a patient with id 1 and after that one with id NULL I get an error.

Is there a way to avoid this? Or do I have to remove all ID's from my insert scripts?

Answer 1

If you provide a (non-null) value for the identity column, the sequence stays at the same value. This means the identity can try and insert a value you provided manually.

There are a couple of paths you could take here

Never supply values for the identity column. Set it as generated always to ensure no-one can do this:

create table patient (
   patientid integer             
      generated always as identity (
        start with 1 nocycle order
      )  not null primary key
);

insert into patient 
  values ( 1 );
  
ORA-32795: cannot insert into a generated always identity column

Allow scripts to provide values, but reset the identity's sequence to the columns maxvalue immediately after using alter table:

drop table  patient 
  cascade constraints purge;
create table patient (
   patientid integer             
      generated by default on null as identity (
        start with 1 nocycle order
      )  not null primary key
);

insert into patient 
  values ( 1 );
insert into patient 
  values ( 11 );
commit;

insert into patient 
  values ( default );
  
ORA-00001: unique constraint (CHRIS.SYS_C0024892) violated
  
alter table patient 
  modify patientid  
  generated by default on null as identity (
     start with limit value 
  );

insert into patient 
  values ( default );

select * from patient;

PATIENTID   
           1 
          11 
          12