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.
Comments
12 responses to “Sequence-less/Trigger-less Oracle Auto Increment”
[…] 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. […]
Great work man,was a very useful help for me.
This statement can be used even when we are using VB6,oracle connectivity
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.
Yup…I know that already. Read the post again and note the last line ;) Thanks anyhow.
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?
[…] 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. […]
I agree with you that Oracle really sucks. MySQL is a better choice.
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.
Not much fun of triggers, but Oracle triggers is total crap.
Because of FOR EACH ROW
Disgusting.
How hard from Oracle to just add the keyword AUTO_INCREMENT or IDENTITY to the identity field? and forget about this sequence/trigger crap?
/* 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.
To all who think Oracle SUCKS, you’ve got no clue what Oracle can do and probably
have no idea about database designing either.
Most non trivial tables have primary key (THE most important column or columns) that uniquely identifies data in rest of the row. In all but trivial application, you’d want a meaningless auto-incrementing number to take its place. Even when you insert a
autoincrementing number, you’d want the value back for foreign key elsewhere.
Almost all databases that support autoincrement encourage a programmer
to abandon these good DB design norms by allowing an easier option of
creating a fake primary key instead.
By not providing Autoincrement, Oracle is forcing designer to THINK..
And that is THE MOST important advantage, I THINK!!!