Sequence-less/Trigger-less Oracle Auto Increment
Oracle sucks. Well, not totally, but it fails in a lot of places where MySQL is sexy. One little gem of an irritation is the lack of an auto_increment attribute associated with fields. Instead you have to make use of Oracle Sequences/Triggers which adds a whole layer of complexity on the creation and insertion into a simple table.
I’m not quite sure on this statement’s efficiency, but here’s my solution:
INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);
EDIT: It seems that this isn’t such a good idea, after help from my friendly neighborhood DBA.
Discuss This Article
|
|
11 Responses to “Sequence-less/Trigger-less Oracle Auto Increment”
-
pingback:
Posted: May 22nd, 2006 at 3:24 pmLife After Coffee » How to Create Auto Increment Columns in Oracle 1 -
gaurav
Posted: Oct 9th, 2006 at 11:53 pmReply to this comment.Great work man,was a very useful help for me.
This statement can be used even when we are using VB6,oracle connectivity2 -
Tim
Posted: Nov 22nd, 2006 at 12:56 pmReply to this comment.Sorry, no.
This won’t work in an envioronment where more than one transaction is open at once. That means you should never ever use it because it’s bad practice and not scalable. (You never really know where it will end up.)
If two users do this at the nerly same time, more specifically if a second user does their insert after the first but before the first user commits, the second commit will fail with a constraint violation. That is, unless the column isn’t unique, in which case you’ll just get duplicate values.
3 -
Matt
Posted: Nov 25th, 2006 at 7:56 amReply to this comment.Yup…I know that already. Read the post again and note the last line ;) Thanks anyhow.
4 -
Sije de Haan
Posted: Feb 17th, 2007 at 1:14 pmReply to this comment.I don’t understand. This problem can be solved by using sequences. But what if two overlapping transactions want to add a value to a field. One of the two updates will get lost?
5 -
pingback:
Posted: Mar 26th, 2007 at 5:04 amLife After Coffee » Re: The web is becoming a dictatorship of idiots - Part 1 Reply to this comment.[...] Here’s a good example where Matt posted what he thought was a good idea of how to create auto-increment fields in Oracle without the use of Triggers. [...]
6 -
Lemine
Posted: Dec 6th, 2007 at 8:34 pmReply to this comment.I agree with you that Oracle really sucks. MySQL is a better choice.
7 -
neel_basu
Posted: Jan 10th, 2008 at 3:12 amReply to this comment.MySQL also sucks at the time of Relation database Modeling with references but thats 100 times easier that this auto increamentation technique of Oracle.
So I think PostgreSql is easiest and Simplest and the best one
can host a Huge ammount of Data like Oracle
NOT a MEMORY HOG Like Oracle
and Its SQL is easier that anything else.8 -
Ingas
Posted: Feb 21st, 2008 at 6:21 pmReply to this comment.Not much fun of triggers, but Oracle triggers is total crap.
Because of FOR EACH ROW
Disgusting.9 -
ralph
Posted: Mar 19th, 2008 at 4:58 pmReply to this comment.How hard from Oracle to just add the keyword AUTO_INCREMENT or IDENTITY to the identity field? and forget about this sequence/trigger crap?
10 -
Adnan Hashmi
Posted: Jun 15th, 2008 at 3:48 amReply to this comment./* Create its trigger */
CREATE SEQUENCE TIME_COMPANY_COMPANYID_SEQ
START WITH 1
INCREMENT BY 1/* My table is **/
CREATE TABLE TIME_COMPANY
(
COMPANYID integer NOT NULL ,
COMPANYNAME varchar2 (100) NOT NULL
)/* And its trigger would be like */
CREATE OR REPLACE TRIGGER TIME_COMPANY_TR
BEFORE INSERT ON TIME_COMPANY
FOR EACH ROW
DECLARE TEMP_NO INT;
BEGIN
SELECT TIME_COMPANY_COMPANYID_SEQ.NEXTVAL INTO :NEW.COMPANYID FROM DUAL;
END;Regards
Adnan Hashmi.11
[...] Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert. [...]