Insert into employee (id, name) values ((select max(id) +1 from employee), ‘name_of_employee’)
This is a very simple solution, however the problem here is that if we have two servers trying to run this query at the same time, things could become a bit messy (in case if the same id value is returned to both these servers).
A clean solution for this is to use a trigger and a sequence. The theory behind this is that we have a sequence that goes through each row of a table and returns the first available value of id. We could then create a trigger that will run when an insert statement is executed with a null id value. This trigger will use the sequence to get the first available id and assign it to the new row being added.
Because this would all be happening at the database level, we will not have to worry about multiple servers (application servers) trying to add a new row to the database (which is usually in a separate database server).
An implementation of the given theory can be as follows:
- A sequence is created that returns the first available id.
- A trigger is created that runs whenever a new row is being added with a null id, replacing it with what the sequence returns.
- A code that uses the following insert statement: insert into employee (id, name) values (null, ‘name_of_employee’).
To create a simple sequence we could use the following code:
Create sequence NAME_OF_SEQUENCE
Minvalue 1
Maxvalue 999999999
Increment by 1
Start with 1
Nocache
Noorder
Nocycle
To create the trigger discussed above, we could use the following sql:
Create or replace Trigger NAME_OF_TRIGGER
Before insert On NAME_OF_TABLE
For each row
Declare
Begin
If :new.ID is null then
Select NAME_OF_SEQUENCE.nextval
Into :new.ID
From dual
End if;
END;
And that's it, a nice clean solution giving a unique id to new rows.
PS: This solution will not reassign the id values of deleted rows, so every time the sequence runs, it starts from where it last ended and not from 1.
