Auto Incrementing?

I'm trying to move from postgre to oracle 9i eventually so I need a little help converting stuff. Is there a way to create a table column to be auto incrementing by just setting the column type and then oracle will create the sequences on it's own?

In PostgeSQL I can simply write...

create table test(id SERIAL, primary key(id));

and that will create the sequences for the id column and make sure it is a primary key.

How would I do so in Oracle?

Unfortunately there is no such data type in Oracle. You can make use of sequences ot write an INSERT trigger to insert a incremental number for that column whenever a new row is inserted.

HTH

a sequences or trigger is needed for the auto increase column

Hi.

This will sort you out:

http://www.oracle-base.com/Articles/8i/AutoNumber.asp

Cheers

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
IF :new.id IS NULL THEN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/

Tim wouldn't it be a little faster that way:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT
ON departments
FOR EACH ROW
WHEN (:new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

Just a little bit :)

Yep. Makes sense. No need to do the work unless you need to.

Alternatively you could do the following but it's still unnecessary if you use your method:

SELECT NVL(:new.id, dept_seq.NEXTVAL)
INTO :new.id
FROM dual;

One thing to consider, by default some engines do not allow you to assign your own id, so you may want to do the following to mimic that type of system:

SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;

Or:

IF :new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified.');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;

I've modified the article to reflect your comments and these suggestions.

Many ways to skin a cat :)

Cheers