My previous post, titled: ‘Sequence-less/Trigger-less Oracle Auto Increment‘ was shot out of the water by my friend and DBA, Jon Emmons. Glad to see that I can be kept in line.
So I have resigned to use Oracle Sequences as a safer means for auto incrementing. But that doesn’t mean that I like it. Here’s what needs to be done to implement auto_incrementing with Oracle:
First, create a sequence:
CREATE SEQUENCE sweet_incrementing INCREMENT BY 1 START WITH 1;
Next write your statement:
INSERT INTO table (id,name) VALUES (sweet_incrementing.NextVal,’bork’);
Now at a second glance, this statement looks much cleaner than my first attempt at subverting the system. However, the fact that you need to create a whole new sequence for each table you wish to have an auto incrementer is pretty stupid when compared to MySQL’s plan of attack. In MySQL you can simply mark a column as a auto_increment during table creation and you’re good to go!
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.