Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
This is probably really low-priority for them because you can "fake" it yourself with sequences and triggers. lifeaftercoffee.com/2006/02/17/… –  Joeri Sebrechts Oct 16 '10 at 13:12
    
Thanks for the hint. I still don't understand why I have to fake it, but it is nice to see that it is not very difficult. –  Sergio Acosta Oct 18 '10 at 2:39
    
And it's not even necessarily to fake it as you can just put sequence_name.nextval in the SQL - e.g. insert into table_name (id_col, col1, col2) values (sequence_name.nextval, 'thing', 'other thing'). I think this isn't something they even see about as a requirement as they've solved the problems which would need it. –  Jon Hopkins Dec 20 '10 at 16:58
    
they heard you they have it now in 12c oracle-base.com/articles/12c/… –  Kalpesh Soni Aug 27 '13 at 21:48

2 Answers 2

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.

share|improve this answer
    
GUIDs as a key can take a boatload of index vs. a properly sized numeric field. As a numeric, the GUID at 128 bits and effectively distributed across its bounds can mean many gibibytes of scan on a mere billion records. –  Xepoch Oct 20 '10 at 19:22
    
They case yes, but as i've commented above to HLGEM they solve a specific complex problem in a simple way. I'm not suggesting that GUIDs be used at all times, but just consider they have an appropriate use. Also: "mere billion records" ha :) –  Thomas James Oct 20 '10 at 20:54

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.

share|improve this answer
2  
Strongly disagree. The idnetity column is the least troublsome in a complex system becasue it doesn't change. And very, very few "natural" keys really are unique and unchanging as they should be. As fara as import/export, etc. I do that daily and it's not hard at all, you just set up a cross match table and you are in business. –  HLGEM Oct 20 '10 at 17:48
2  
@HLGEM: Guid also don't change and you don't need "a cross match table". "A cross match table" is not that obviously hard to handle, but think about an importing process taking constant memory/storage vs. linear memory/storage proportional to the data being imported. I worked on a project which involves transferring about 10 tables between handhold devices. To maintain referential integrity, I need to keep about 10 "cross match tables" in memory while processing. You may argue that that was a bad design since beginning, –  Codism Oct 20 '10 at 17:59
    
@HLGEM: (sorry for not being able to complete my last comment, every time I use "END" I realize the comment is submitted). To continue - You may argue that that was a bad design since beginning, but who sees the requirement in future? –  Codism Oct 20 '10 at 18:09
2  
GUIDs can significantly slow down performance compared to integre identities. And I haven't yet seen anyone who wants to write queries using them in the where clause. I can ask a user to give me the person_id of the record (Name not being a unique identifier) that they think is a problem, I can't even show them a GUID or the average user freaks out. GUIDs are far worse on a day to day basis than identities. So to solve a happens-every-once-in-a-while problem, you permanently cripple you system. Seems short-sighted to me. Now if you are doing replication yes you need them. –  HLGEM Oct 20 '10 at 18:19
2  
@HLGEM: I've got to agree with Codism, GUID's make a disconnected system easier to manage over identity columns when you have devices that are "sometimes-connected". –  Thomas James Oct 20 '10 at 20:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.