java - Can we lock on non existing data in Oracle?

Question

Our use case is something similar to this : First we need to check if an active record exists with the given column (non unique), if it does not then create one. Unfortunately duplicate inserts have been happening because of concurrent requests .

Sample schema/Data :

ID AccountNumber Status
1  12121321323   Y
2  97867585596   N

So if accountNumber with Status Y does not already exist in the table we need to create one. Is there anyway we can ensure that only one record exists with (AccountNumber , Y as status) ?

Any help is appreciated !! Thanks in advance.

question from:https://stackoverflow.com/questions/65846785/can-we-lock-on-non-existing-data-in-oracle

Answer 1

You can create a unique index on the table

create unique index only_one_active
    on your_table( case when status = 'Y'
                        then accountNumber
                        else null
                     end );

That will allow you to have as many rows as you like with the same account number and a status of N but only one row per account number with a status of Y.