At work I was working (imagine that) on creating a new procedure in the database to escheat checks based on certain rules. Explaining what exactly was going on would be getting into to much accounting for my taste so we’ll continue on to the real problem. So as the first step in this process I created a table with the rules in it. The period of time a check stays out before being escheated varies by state so each state had a row in the table. Now, I had several columns, the full state name (West Virginia), the postal code for the state (WV) and number years for each type of payment (bonus, royality, etc). Now the problem here is I didn’t want to have to insert a key for each row, I find that stupid personally. If all the primary key is in this case is just an enumeration of the rows then I see no need for me to come up with that number, it should be calculated and applied by the database.

In MySQL this would be easy. You can simply set your primary key field as type AUTO_INCREMENT and be done with it. Now when you insert a row into the table you simply leave the PK field out of your insert statement and the key is automatically calculated and inserted for you.

In Oracle though, this is not so easy. First off and foremost there is no AUTO_INCREMENT type. You have ENUMs, which are handy, but not what we want. So how do you get around this? Well you have to do what I consider a hack. First I created the table using proper types and setting my PK. [I stress my use of keys and types because I see a lot of people not doing that and it seriously irks me.] Then I created a SEQUENCE. I gave it a silly, yet descriptive, name.

CREATE SEQUENCE state_seq
START WITH 1
INCREMENT BY 1;

So far so good. I then created a trigger to go with my sequence. So every time a row was inserted into table the trigger would insert the next number in my sequence with it.

CREATE OR REPLACE TRIGGER state_trg
BEFORE INSERT
ON scheme.state
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT state_seq.nextval INTO :NEW.column_pk FROM dual;
END;

Now, looking back at MySQL wouldn’t it just be easier to have AUTO_INCREMENT in Oracle? They make millions (if not billions) every year off this stuff and they can’t put this and command recall into SQLPlus? Really?

Enjoy the Penguins!

See Also: Auto_Increment Part 2