Take the 2-minute tour ×

I am trying to use Dennis' solution here as an implementation of auto_increment in Oracle database. Say I create one sequence as follows:

CREATE SEQUENCE auto_increment

If I want auto_increment behavior in multiple tables, can I just use this sequence for all tables? Or do I need a separate sequence per table? That is, will the sequence increment for one table be affected by another table using the sequence?

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Yes, the sequence accesses will be affecting each other if you use the same sequence. However the tone of your question makes me think that you expect the sequence to be continuous.

Don't be fooled, sequences are NOT sequential. The only thing that you can be garanteed is that the numbers retrieved are unique, and in an ascending order (in your case)

share|improve this answer
And even ascending order isn't guaranteed by the DDL that was posted (since the database is potentially using RAC). –  Justin Cave Jul 30 '12 at 18:42
So, even if only one value in one table is using the sequence with an insert trigger, I am not guaranteed the sequence 1, 2, 3, ..., n? But there may be gaps? –  jwest Jul 30 '12 at 18:53
exactly, confirmed today once again –  Sebas Jul 30 '12 at 18:57
add comment

You can use the same sequence for many tables. It would be unconventional to do so, it would lead to more contention on the sequence, and it would make life a bit more difficult if you needed to reset the sequence value as a result of, say, an export and import between environments but it would work.

Of course, if the sequence gave a value of 1 for table A, it would never give that same value to a trigger defined on B. Since sequences do not generate gap-free sets of values (i.e. you can guarantee that there will be "missing" values in every table no matter how many sequences you create) that shouldn't be a major downside.

share|improve this answer
add comment

Sequences are sequential. However, there are many things that can cause gaps in the sequence e.g rollback, commit (because the sequence generator issues sequences irrespective of commits or rollbacks), and same sequence for multiple tables.

share|improve this answer
add comment

Your Answer


