Custom Sequence using Trigger & Sequence in Oracle SQL
Gap Free Sequential Number

What you seek can be referred to as a "gap free sequential number".

These types of Business Requests are known to slow down performance for the tables involved.  This is due to the required serialization on the entity that generates the number.

Parent Table

PARENT_ITEM.ID is not going to be a "gap free sequential number"; you are missing the NOCACHE option.

Even then, you can still have gaps.

Child Table

I recommend you keep CHILD_ITEM.ID as a surrogate key.  I would keep the defaults for the Identity column.  Also, you should have a separate column for the numerical value that represents "-1".  I'll call this column CHILD_NUMBER_SEQUENCE.

Now, to serialize the "number generator" for CHILD_SEQUENCE, you must obtain a lock based on PARENT_ITEM_ID.

For Oracle, the easiest way is to lock the parent row.  If the table PARENT_ITEM maintains "current child sequence number" the algorithm becomes easy since you can "lock the parent row" and "get the next value" at the same time.

Table Modifications

alter table parent_item add ( current_child_sequence_number number(5) default 0);
alter table child_item add (child_sequence_number number(5) not null);
alter table child_item add
  constraint child_item_uq1 unique (parent_item_id, child_sequence_number);


Code to generate `CHILD_ITEM.CHILD_SEQUENCE_NUMBER'

-- this locks and updates the parent row.
update parent_item p
set p.current_child_sequence_number = nvl(p.current_child_sequence_number,0) + 1
where p.id = :new.parent_item_id
returning p.current_child_sequence_number into :new.child_sequence_number;
-- make sure you check that 1 and only 1 row was updated.
-- fail if that isn't true.


Final Notes

The code is specific for single row processing.

If you want performance for bulk loading, you will have to use a staging table (eg Global Temporary Table) and a Procedure.

Again, the Business Requirement of "gap free sequential numbers" is slow by nature.  Don't expect it to be fast.