One of the tedious task I have to deal with when working with Oracle is when I need to create an auto-increment column. At first I was expecting this kind of task can be accomplished easily, just like in MySQL or SQL Server, but apparently things are a little different in Oracle.
For those who doesn’t know it yet, auto-increment column in Oracle is done using a trigger and a sequence. Compared to IDENTITY in SQL Server or AUTO_INCREMENT in MySQL, I found this a little bit too demanding 🙂
Fortunately, there is a way to automate this task using a front-end application for Oracle called Oracle Sql Developer. I’m using it on my Windows machine, but since it is Java-based, it should be available too in Linux or Mac. Here’s how I do it :
- Create a new table.
- Give the table a name, and make sure the Advanced check box is checked.
- Give the identity column a reasonable name. You should also specify an appropriate type for this column.
- You might also want to specify the identity column as a primary key. Go to the primary key menu, and ensure the column is placed on the right-most list box.
- Now for the auto-increment itself. Go to the column sequences menu, pick the identity column, and check the box that says populate column bla bla bla.
To ensure that the value of this column is always auto-generated by the system, uncheck the checkbox at the bottom.
Oh, one more thing. This menu is only available for new table. That’s why you should follow this instruction from the first step 🙂
- Go back to the columns menu, and complete the column specifications. When you happy with your new table, click OK to save the table. Alongside with the table, you will also get a trigger and a sequence for the auto-increment mechanism.
Now you can have an auto-increment column without having to manually create the trigger and sequence. I hope you will find this useful.
My Blog 