8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
AutoNumber And Identity Functionality (Pre 12c)
Oracle Database 12c has introduced two new features that make the trigger-based solution for populating numeric ID columns redundant. If you are using Oracle 12c onward, you should consider one of the following options.
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- DEFAULT Values Using Sequences in Oracle Database 12c Release 1 (12.1)
If you are using a version of the database prior to Oracle 12c, this article describes how to implement AutoNumber/Identity type functionality in Oracle.
Developers who are used to AutoNumber
columns in MS Access or Identity
columns in SQL Server often complain when they have to manually populate primary key columns using sequences in Oracle. This type of functionality is easily implemented in Oracle using triggers.
Create a table with a suitable primary key column and a sequence to support it.
CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq;
Create a trigger to populate the ID
column if it's not specified in the insert.
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; /
As described here, from 11g onward CURVAL
and NEXTVAL
sequence pseudocolumns can be used directly in PL/SQL expressions, so you could instead do the following.
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW WHEN (new.id IS NULL) BEGIN :new.id := dept_seq.NEXTVAL; END; /
Test it using the automatic and manual population methods.
SQL> INSERT INTO departments (description) 2 VALUES ('Development'); 1 row created. SQL> SELECT * FROM departments; ID DESCRIPTION ---------- -------------------------------------------------- 1 Development 1 row selected. SQL> INSERT INTO departments (id, description) 2 VALUES (dept_seq.NEXTVAL, 'Accounting'); 1 row created. SQL> SELECT * FROM departments; ID DESCRIPTION ---------- -------------------------------------------------- 1 Development 2 Accounting 2 rows selected. SQL>
The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like the following.
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW BEGIN IF :new.id IS NULL THEN SELECT dept_seq.NEXTVAL INTO :new.id FROM dual; END IF; -- Do more processing here. END; /
To overwrite any values passed in you should do the following.
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW BEGIN SELECT dept_seq.NEXTVAL INTO :new.id FROM dual; END; /
To error if a value is passed in you should do the following.
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW BEGIN 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; END; /
For more information see:
- AutoNumber, Identity and Using Sequences as Default Values in Oracle
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...