0

Is there a way in oracle to create a column with auto-increment and if a row is deleted the next value that's been deleted should replace the row that is deleted. Is there a way to do that in oracle?

Patrick Bacon
  • 4,490
  • 1
  • 26
  • 33
blank
  • 21
  • 5
  • Why would you do that? You have a sequence to provide that incremental value for a column but... to replace old rows, it would be a) no good on concurrent users, b) overkill (quite complex to avoid jumping into already used values. Also, where's the relationship of oracle apex with your question? – Alfabravo Nov 03 '17 at 16:48
  • You can use Sequences in oracle to define an auto-increment value. You wouldn't want to use replacements like this for primary keys or ids. However Oracle has a built in rowid that you can get by `select rowid from your_table_name` – Dean Nov 03 '17 at 16:50
  • Possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – Raymond Nijland Nov 03 '17 at 16:51
  • i just want to create a primary key for the reports on oracle apex but when im deleting some values and inserting another the sequence is continous like skipping the number that is been deleted – blank Nov 03 '17 at 16:55
  • i tried that sequence and trigger but my problem is if i delete a row id=2 and insert a new one =3 so that id no 2 is being blanked – blank Nov 03 '17 at 17:18

1 Answers1

0

That behavior y ou are describing (having "holes" in the sequence after deletes) will always happen with SEQUENCE. For most applications, it is a good thing and works perfectly because most of the time, the id of the table is artificial and meaningless. Its only use is to connect tables with relations and for that, holes are unimportant.

In your case, if you want to create a continuous sequence and fill gaps if they are created, you need to create a trigger on insert that updates your ID with the value of the first "hole" found in your sequence using a SELECT like this :

SELECT MIN(tb.id) + 1 "first_seq_hole"
  FROM yourTable tb
 WHERE NOT EXISTS
 (SELECT tb.id FROM yourTable tb2 WHERE tb.id + 1 = tb2.id)

Although, I am not sure what is your requirement here but at some point you might still have holes in your sequence (say you delete 10 random rows and never insert any to fill them). That's unavoidable though unless you work on a way to change existing IDs to instantly fill gaps when rows are deleted. It would be complicated and risky if you have child tables using that ID though.

Dessma
  • 599
  • 3
  • 11
  • sir sorry but i didn't understand the query much but i think that is the answer on my problem – blank Nov 03 '17 at 18:00
  • the query returns the first hole in a sequence contained in a field. Say you have [1,2,4,6] because you deleted [3] and [5], what you want is to insert [3] next. The query is built to check which one is the first value in your sequence (`min(tb.id)`) that has no "following" value (the `NOT EXISTS` part). It then adds 1 to that value and you obtain the first free value in your sequence. You need to replace yourTable by your table name and the id column with your real id column name. – Dessma Nov 03 '17 at 18:05