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.