Oracle Autoincrement

Posted on Aug 1, 2005 in Technical Tips | 0 comments



Unlike Oracle, other less expensive and less robust databases make autoincrementing a simple task…typically a couple of clicks in some easy to understand UI. Some examples are with SQL Server, MySQL, and Access. As we know, with Oracle’s additional “flexibility” can come additional work (but this is sometimes for a legitimate reason).

Approach 1

Following are the steps on how you can mimic an autoincrement in Oracle:

1) create a table
2) create a sequence
3) create a trigger
4) insert record(s)

CREATE TABLE test_table ( test_id INT PRIMARY KEY, test_col varchar2(100));
 
CREATE SEQUENCE test_seq;
 
CREATE TRIGGER test_trigger
BEFORE INSERT ON test_table
FOR each ROW
BEGIN
  SELECT test_seq.NEXTVAL INTO :NEW.test_id FROM dual;
END;
 
INSERT INTO test_table ('test value');

Approach 2

**Important Note: Incrementing the sequence inside the SQL statement (not actually autoincrementing) is typically considered the cleaner approach and allows for better performance. It can be achieved as follows:

CREATE TABLE test_table ( test_id INT PRIMARY KEY, test_col varchar2(100));
 
CREATE SEQUENCE test_seq;
 
INSERT INTO test_table (test_seq.NEXTVAL, 'test value');

Want a chance at learning more and also a potential job offer?  Leave a comment or contact us using the form to your right explaining why the second approach performs better than a trigger with quantitative analysis.

Bookmark and Share

Related Information:

  1. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training
    APEX gives you a lot...
  2. Oracle APEX Tutorial 8 – Up and Downloading Files – Part 1 – Video Training
    Reports and data entry constitue...
  3. Oracle APEX Tutorial 8 – Up and Downloading Files – Part 2 – Video Training
    Reports and data entry constitue...
  4. Oracle Rename Table – Example Syntax and Dependencies
    Renaming a table in Oracle...
  5. Copy Table Columns and Data
    As you may know, in...
  6. alter table, add column – Oracle Example Syntax
    Following is simple example syntax...
  7. Oracle APEX Advanced Tutorial 1 – Creating a Tabular Form – Video Walkthrough
    This tutorial is designed to...
  8. Oracle Rename Column – alter table, rename column Example SQL Syntax
    Following is simple example syntax...

Leave a Reply