-
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
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
a sequences or trigger is needed for the auto increase column
-
-
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
Last edited by TimHall; 08-22-2003 at 10:19 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|