Search code examples
oracle10goracle11gsequences

In Oracle, is using Sequences for surrogate primary keys considered standard

Caveats:

  • Let me first clarify that this is not a question about whether to use surrogates primary keys or not.

  • Also, this is NOT a related to identities (SQL Server) / Sequences (Oracle) and their pros / cons. I did get a fair bit of idea about that thanks to this, this and this

    Question:

    I come from a SQL Server background and have always used identity columns as surrogate primary keys for most tables.

    Based on my knowledge of Oracle, I find that the nearest equivalent in Oracle are SEQUENCES which can be used to simulate something similar to Identity in SQL server.

    As I am new to Oracle and my database has 100+ tables, the main thing that i am concerned about :-

  • Considering i have to create a sequence for each table in Oracle (almost), would this be the standard accepted implementation for simulating Identity or is there a better / easier way to achieve this kind of implementation in Oracle?
  • Are there any specific GOTCHA's related to having so many sequences in Oracle?

    The system supports both Oracle 10G and 11G

    • Solution

  • Considering i have to create a sequence for each table in Oracle (almost), would this be the standard accepted implementation for simulating Identity or is there a better / easier way to achieve this kind of implementation in Oracle?

    Yes, it is very typical in Oracle to create a sequence for each table. It is possible to use the same sequence for several tables, but you run the risk of making it a bottleneck by using a single sequence for many/all tables: see this AskTom q/a

    Are there any specific GOTCHA's related to having so many sequences in Oracle?

    None that I can think of.