Oracle’s Auto Incrementing with Sequences

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!