Oracle's auto increment
The Oracle Database Management System doesn't have an auto-increment
column type, we all knew that.
And some of the later Application development literature frown upon
the idea of leaving the DB system to generate unique IDs for tupples
in a database (tupples = rows in this case). The main reason is due
to coupling of the Application and the DB system being used. There shouldn't
be any coupling between the two, the story goes.
For example Entity Beans (EJBs that is) in J2EE has Container Management
Persistence, which makes serialization of Object and Relational databases
transparent - Ok, there are some complexities issues, since having all
these flexiblity and transparency doesn't come cheap - It's a bit more
complex to code and there's a performance hit, whatever minimal it is,
it still exists. However, when and Entity EJB is coded properly to use
CMP, it is a beatiful thing: The Enterprise Application Server does
all of the SQL stuff and the developer only concentrates in solving
business problems. No more SQL scripting.
We all know that the ideal ussage of a tool is not always the factual
reality. Some session beans are still needed to do some ad-hoc database
stuff, so we must still code SQL embeded in our Java classes (Hopefully
using JDBC). So, the reality is that, RDBMS are still going to be used
with newer OO Development techniques.
I will mention one way to create a unique id for a column type INTEGER
for an Oracle database. Note that there are many different ways of doing
this. For example, you could have the application generate a unique
id with some kind of run time utility.
In this case, I'm presenting to you one way of faking an Oracle auto-increment
column type. In a nutshell:
1. Use a sequence
2. Use a trigger
Get to the code, already
CREATE TABLE my_table (
/* Sequence to autoincrement */
CREATE SEQUENCE seq_for_my_table
START WITH 1;
/* Trigger to auto increment row_id */
CREATE OR REPLACE TRIGGER my_table_trigger_insert
BEFORE INSERT ON my_table
FOR EACH ROW
DECLARE m_no NUMBER;
SELECT seq_for_my_table.nextval INTO :new.row_id FROM dual;
INSERT INTO my_table (user_id, data)
VALUES ('jose', 'This is some data');
So, where is row_id? It is being auto incremented! Simple as that.
There are reasons not to couple your DB and your Application,
but most application require this coupling. Otherwise, Microsoft would
not sell a whole business solution to generic problems. Why would Oracle
want you to use Oracle products?
Java J2EE promises to rid of this coupling, but, the truth is that is
not the case. I'm still waiting for the "write once, run anywhere"
promise to be fullfilled.
Anyway, if you are using an Oracle database and want to have an auto
incremented column (handy sometimes), this is one way of doing it.
I hope this tip is of some use to some one.