Oracle SQL sequence

Question

I made a sequence in Oracle and when I delete a row (making active 0) in java desktop, deleted row became invisible in desktop. But it stays in database. That's why, when I try to insert a new row in desktop, the sequence considers deleted id too and id sequence id desktop becomes 1, 3, 7 etc. How can I fix it?

• JB Nizet: A sequence doesn't care about rows in your table. It's just a sequence. It increments each time you ask for the next value. Reusing IDs is a terrible idea anyway, so you really shouldn't try doing that.
• This Immortal: I know that. But deleted row doesn't seem in java desktop, but it exists in database due to making its active 0. And when I insert a new row in java desktop, sequence automatically increments. For example 1,2,3,4. But 3rd row has been deleted in desktop, that's why in desktop it becomes 1,2,4. But in database all is ok.
• JB Nizet: OK, so, if you know that, why are you surprised by the behavior you're seeing? What is your question? There is nothing to be fixed: it's normal, and expected, and desired behavior.

Answer 1

Assuming the ID is unique and isn't deleted as you describe, you don't have any choice other than considering also deleted records, because it a valid record and use of key even if application wise it's not a valid record.

You can change your primary key to using both status and id column, and but that allow yourself to add records with deleted ids manually with different/active status

Answer 2

As @JBNizet mentioned, a sequence is just that: a sequence. It's not an IDENTITY field and if you do select mysequence.nextval from dual; multiple time it will just keep going up.

• JB Nizet: The same would be true for an IDENTITY field.
• Simon Berthiaume: @JBNizet Since Oracle's IDENTITY fields were only added in 12c, my guess is that it's just implemented as syntactic sugar over a sequence, so it would make total sense to have the same behavior.