5

EDIT: As gavenkoa's answer points out, Oracle Database 12c (released a couple of years after this question was asked) has support for Identity Columns.

As far as I know, Oracle's RDBMS is one of the few (the only?) SQL database products that doesn't support identity/autonumeric columns.

The alternative offered by Oracle is database sequences, a feature in many ways much more powerful than auto-numeric columns, but not equivalent.

It is not that I don't like sequences. What I hate is having a different programming model for generating row identity values between Oracle and any other database. For example, I often try to setup HSQL or SQLite for java apps that will eventually run over an Oracle database when I'm not working specifically on the data layer (just as a stub or mocking database). I cannot do that easily because I need different set of SQL DDL scripts: one for Oracle, and one for everyone else; I also need two sets of Hibernate mapping files if I'm using Hibernate.

What I find intriguing is that Oracle Database, being one of the most complete and robust enterprise software packages of the last decade hasn't put that seemingly basic feature in their product, but almost any other RDBMS, even the smaller ones, has it.

Why?

Why doesn't oracle support a sequence-based identity column shortcut syntax that dumb and lazy people like me can use?

The only reason I can think of is that Oracle does that on purpose as a vendor lock-in strategy so your code is harder to migrate to other RDBMS where your database sequences cannot be used.

Or maybe I'm just wrong and confused? Please enlighten me.

Community
  • 1
Sergio Acosta
  • 9,558
  • 3
  • 26
  • 36

4 Answers4

5

http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
gavenkoa
  • 271
  • 2
  • 10
3

I'm only guessing here but its probably for legacy reasons.

Sequences & identity columns have annoying properties like not respecting transactions. Sequences actually provide you with more flexibility than a plain identity column as it allows you, the developer, to decide how and when to apply the sequence.

Sequences also give you the ability to know your assigned sequence number before having to insert the record.

On a side note, if you plan in the future to support either replication or any form of disconnected'ness (eg mobile devices or offline connections to your database) i would suggest using GUIDs as your key. As this removes issues around sequence partitioning etc.

Thomas James
  • 697
2

The priorities of Oracle are just way different from the once of the everyday developer.

From my experience I'd categorize Oracles attitude as follows:

Top Priority

  • Reliablity: If one thing makes you look bad in the RDBMS world, it is destroyed data, broken backups and select statements returning wrong data.

  • Performance: The customers that pay really big bucks (and who cares about the rest?) has huge databases and a lot of work to do. So things that make large databases fast are features that sell themselves

Low or No Priority

  • Developer usability: If a developer wants to work on something nice and fancy, she left RDBMSes a long time ago. So the rest doesn't mind jumping through hoops for some basic task. They also aquired so much specialized Oracle know how, that switching systems is difficult.

There a lot of thing that fall into this category: tool integration with Version Control systems; management for database migrations; Boolean datatype for table columns; Identity columns.

For all these things and probably many more there are some workarounds / patches that somehow get the job done. As in the case of sequences even more powerful and flexible, but inconvenient. But since almost no paying customer chooses the RDBMS based on its niceness to the developer Oracle doesn't care.

To get an impression of the mindset of Oracle (and of many developers/admins working with or for Oracle) let me quote a piece from Tom Kyte:

You Asked

Here's a real short one for you Tom:

Why doesn't Oracle RDBMS have a boolean datatype?

and we said...

since

...,
flag char(1) check (flag in ( 'Y', 'N' )),
...,

serves the same purpose, requires the same amount of space and does the same thing - I guess we feel this is a feature we can let them have that we really don't need.

I mean - what do you get back from a column in "access" that is a boolean? TRUE / FALSE. We'll give you Y/N -- if you would like TRUE/FALSE, we can accomplish that easily with DECODE(flag,'Y','TRUE','N','FALSE')

Which I personally translate to: Who needs types? Assembler is just fine.

Jens Schauder
  • 1,513
1

I don't know about sequence in Oracle db but I try to avoid using identity columns. Usually identity columns are used as surrogate keys and seem working well at beginning but sooner or later when business requires to transfer/export/import data between systems and clients, identity columns are the one that is most troublesome to deal with.

Codism
  • 1,213