One of the things I like about MS Access is the AutoNumber field type. And SQL Server has its Identity field type. Very handy for primary keys. But from time to time I convert applications into a more heavy-duty database, such as Oracle, where to do the same thing, you need to select from a sequence first. Here’s how to do an Autonumber-style field without mucking about with the sequence.

You do, of course, need the sequence, to track unique IDs.

CREATE SEQUENCE [Schema].[SequenceName] START WITH [Number] INCREMENT BY [Number] MINVALUE [Number] NOCACHE NOCYCLE;

NoCache will avoid skipping values due to Oracle creating any numbers in advance. NoCycle avoids the number repeating itself. I generally name my sequence after the field it’s being used for, with an _SEQ suffix.

Putting the sequence number into the field is done by a trigger:

CREATE TRIGGER [TriggerName]
before insert on [TableName]
for each row
begin
select [SequenceName].nextval into :new.[ColumnName] from dual;
end;

Easy!

There is a catch. While in Access or SQL Server there are easy ways to find out the value of the AutoNumber/Identity field in the row you just added (through @@IDENTITY in SQL Server or in Jet 4.0 or later), this isn’t so easy in Oracle.

You can query the column’s MAX value or the sequence CURRVAL afterwards — but this isn’t reliable if yours is not the only process inserting data. Would it work if you placed your code in a transaction? I’m not convinced.

So the trigger method should only be used when you don’t need to know the number entered straight afterwards. If you need to know, then forget the trigger — just select your Sequence value first, then use that in your insert(s).

As someone who administers a number of databases, I’d say to anyone that you need a damn good reason to put non-standard values into a field.*

Today’s curse is sorting out the “Counties” field, which is supposed to be used to show British counties. To add insult to injury, people have to select from a drop-down menu, and if they try to type in a value that doesn’t exist, they are specifically asked if they mean to add that value (methinks the database is not locked down quite tightly enough, but I can’t control that bit at the moment).

People, let me tell you, from even basic general and geographical knowledge: Austria, Australia, BD20 0DX, Bahrain, Brentwood, Brighton, Jacksonville Canada, NW10 0HD… none of these are British counties, nor were they ever, nor will they ever likely be.

* That good reason doesn’t exist. The same goes for towns, titles, gender, and a whole host of other fields with clearly defined standards.