Oracle SQL automatic ID generation

When developing an application at one point you most likely will come to the subject of providing ID's. When you store a record in the database you most likely will like to have an ID for this record so you can uniquely identify it and make relations to records in other tables based upon this ID.

When creating the design for your application and associated database model you can select 2 locations to generate the ID used for a specific record. One option is to have your application query the next number in a sequence in the database and use this the second is to have the database populate this when you enter the record. Depending on some criteria you can select both options however I am personally in factor of having your database handle this. As in most cases an ID is directly influencing your data model integrity I do favour having this done here however in some cases there is a good reason to deviate from this.

Considering that you will use the database to populate your ID's for you, a coupe of things are needed: first we need the table where we will store the data, secondly we need a sequence to give you the next number and we need a trigger on the database table. The trigger will be used to query the next number from the sequence and use this number during the insert in the table. When you are using Oracle SQL Developer you can use the "Column Sequences" option to have this all done for you. When using this option a trigger and a sequence will be created and associated with the column you want to populate with a ID. This is shown in the example below:

Looking at the code generated you will notice that a trigger and a sequence are generated. In the case of the example above we have a new trigger named AUTO_ID_EXAMPLE_0_TRG
 and a sequence named AUTO_ID_EXAMPLE_0_SEQ

Code for the trigger:

create or replace
TRIGGER AUTO_ID_EXAMPLE_0_TRG BEFORE INSERT ON AUTO_ID_EXAMPLE_0 
FOR EACH ROW 
BEGIN
  BEGIN
    IF :NEW.EXAMPLE_ID IS NULL THEN
      SELECT AUTO_ID_EXAMPLE_0_SEQ.NEXTVAL INTO :NEW.EXAMPLE_ID FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

Code for the sequence:

CREATE SEQUENCE "LOUWERSJ"."AUTO_ID_EXAMPLE_0_SEQ" 
MINVALUE 1 MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

Now we can test to see if there is indeed an automatic ID generated when we insert records into the table. We insert the following records as a test, do note we only insert data in the example_text column and not in the example_id column.

insert into auto_id_example_0 (example_text) VALUES ('TEST1');
insert into auto_id_example_0 (example_text) VALUES ('TEST2');
insert into auto_id_example_0 (example_text) VALUES ('TEST3');

When we query the table we do get the following result:

SELECT * FROM auto_id_example_0;

EXAMPLE_ID           EXAMPLE_TEXT       
-------------------- --------------------
1                    TEST1                
2                    TEST2                
3                    TEST3    

having a meaningless number as an ID can be very good and is commonly used as a database internal reference however when we are building an application where the number is used as a reference towards humans commonly the number is more meaningful and/or it is changed to be more human friendly. For example, if you check order numbers or ticket numbers it is commonly seen that you have some padding on the left side. Meaning, you will not see the number 1024 for example, you will see more likely on your order the number 00001024 or even more meaning where you have for example ORD_UTR_00001024 where for example ORD is showing we are talking about an order and UTR is showing that the order is generated in a shop with the code UTR.. An automatically generated ID can be anything and is not limited to a number, you can code all meaning you like in the trigger.

As an example for the 00001024 example we could construct the following trigger instead;

create or replace
TRIGGER AUTO_ID_EXAMPLE_0_TRG BEFORE INSERT ON AUTO_ID_EXAMPLE_0 
FOR EACH ROW 
BEGIN
  BEGIN
    IF :NEW.EXAMPLE_ID IS NULL THEN
      SELECT LPAD(AUTO_ID_EXAMPLE_0_SEQ.NEXTVAL,8,0) INTO :NEW.EXAMPLE_ID FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

Within the trigger we do use the LPAD function from Oracle SQL to left-pad the number generated by the sequence with a 0 until we reach a length of 8 characters. If we now insert a new record the next example_id will be something like 00000004 (or 00001024).

Now we have the situation where we would like to give some more meaning to the number and we would like to have something like ORD_UTR_00001024. In the below example we have added some additions to the trigger. A subquery is introduced to query the UTR part and we have added ORD_ in front. In the below example the subquery is not making any sense because it will always return UTR however you can introduce your own business logic to it.

create or replace
TRIGGER AUTO_ID_EXAMPLE_0_TRG BEFORE INSERT ON AUTO_ID_EXAMPLE_0 
FOR EACH ROW 
BEGIN
  BEGIN
    IF :NEW.EXAMPLE_ID IS NULL THEN
      SELECT 'ORD_'
            ||
            (SELECT LOCATION_CODE FROM AUTO_ID_LOCATION WHERE LOCATION_CODE = 'UTR')
            ||'_'
            || lpad(AUTO_ID_EXAMPLE_0_SEQ.NEXTVAL,8,0)
      INTO :NEW.EXAMPLE_ID 
      FROM dual;
    END IF;
  END COLUMN_SEQUENCES;
END;

By using a more sophisticated trigger you can ensure that your automatically generated number by the sequence is more meaningful then only a number. As you will notice when developing applications it is quite a common request from business owners that ID's like order numbers or ticket numbers are not simply numbers however have some more meaning. When looking for a way to build this you can opt to build this into you application side however as you can see there are a lot of options in the database and to my personal believe this is the place where this should be done by default with the exception if there is a very good reason not to do this.