FB

How Can You Create A Column With AUTO_INCREMENT in Oracle SQL?

If you’ve used MySQL, you’re probably familiar with the AUTO_INCREMENT feature. But it’s not available in Oracle. Read this article to find out how you can auto increment a column in Oracle SQL.

What Is Auto Increment?

An auto increment column, or an identity column in other databases, is a column that has its value automatically increased with every row that is inserted.

It’s most commonly used for primary keys or ID fields, where you need a different number for each row and it should be generated easily.

When you define a column in MySQL, you can specify a parameter called AUTO_INCREMENT.

Then, whenever a new value is inserted into this table, the value put into this column is 1 higher than the last value.

But, Oracle does not have an AUTO_INCREMENT feature. How can you do it in Oracle?

 

How To Create an Auto Increment Column in Oracle 11g

If you’re using Oracle 11g or earlier, you create it in a different way than if you are using Oracle 12c.

To create an auto-incrementing column in Oracle 11g, you can follow these steps:

  • Create a sequence
  • Create a BEFORE INSERT trigger that uses this sequence

You can find all of the scripts used in this article on my GitHub repository here.

So how do we do this?

First, create a sequence.

What is a sequence?

A sequence is an object in Oracle SQL that lets you generate unique numbers.

Let’s create one now.

CREATE SEQUENCE student_id_seq;

Give your sequence a name that makes sense. I’ve called mine student_id_seq.

Next, create a trigger. What is a trigger?

A trigger is an Oracle SQL object that runs on a certain condition.

You can read my guide to Oracle triggers here.

In this case, we’re creating a BEFORE INSERT trigger, which runs before a record is inserted into a specific table.

CREATE TRIGGER student_bi
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  SELECT student_id_seq.nextval
  INTO :new.student_id
  FROM dual;
END;

With this code, whenever you insert a record into the student table, this trigger will run. It will replace the student_id value you have specified, even if you haven’t specified one, with the NEXTVAL from the sequence. This NEXTVAL is the way that the next value is obtained from the sequence.

Let’s test this.

First, select all of the records from the student table. You may have noticed I’ve called the table student and not students, as I recommend using singular table names.

SELECT student_id, first_name, last_name
FROM student
ORDER BY student_id;

Result:

No results found.

As you can see, there are no records in the table. I’ve created this table from scratch.

Let’s insert a new record, without specifying the sequence value.

INSERT INTO student (first_name, last_name)
VALUES ('Jason', 'Smith');

Now, let’s select the data again.

SELECT student_id, first_name, last_name
FROM student
ORDER BY student_id;

Result:

STUDENT_ID FIRST_NAME LAST_NAME
1 Jason Smith

Take note of the student_id value.

Let’s insert another record.

INSERT INTO student (first_name, last_name)
VALUES ('Joan', 'Colbert');

And, select from the table again.

SELECT student_id, first_name, last_name
FROM student
ORDER BY student_id;

Result:

STUDENT_ID FIRST_NAME LAST_NAME
1 Jason Smith
2 Joan Colbert

You can see that even though we didn’t specify a student_id value, a unique value has been entered. This is because of the sequence and the trigger.

 

How To Create an Auto Increment Column in Oracle 12c

If you’re running Oracle 12c, you’re in luck. It’s much easier to create an AUTO_INCREMENT column in Oracle 12c.

You do this by creating an IDENTITY column.

This is done using the following code in your CREATE TABLE statement:

CREATE TABLE student12c (
student_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);

This means that whenever you insert a record into this table with a NULL value for student_id, it will be generated for you.

INSERT INTO student12c (first_name, last_name)
VALUES ('Jason', 'Smith');

We can then SELECT from the table.

SELECT student_id, first_name, last_name
FROM student12c
ORDER BY student_id;
STUDENT_ID FIRST_NAME LAST_NAME
1 Jason Smith

 

INSERT INTO student12c (first_name, last_name)
VALUES ('Joan', 'Colbert');

SELECT student_id, first_name, last_name
FROM student12c
ORDER BY student_id;

 

STUDENT_ID FIRST_NAME LAST_NAME
1 Jason Smith
2 Joan Colbert

It has the same result, but it’s simpler. You don’t need to create a trigger for auto_increment columns in Oracle 12c.

 

Conclusion

So, in conclusion, the way you create use AUTO_INCREMENT in Oracle SQL depends on your Oracle version.

  • For 11g and earlier, you create a sequence, create a BEFORE INSERT trigger, and call the NEXTVAL value of the sequence within the trigger.
  • For 12c, you can specify a column as IDENTITY when you create your table.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

12 thoughts on “How Can You Create A Column With AUTO_INCREMENT in Oracle SQL?”

  1. Thank you! How do you get the value of employee_id, that was recently inserted, to populate as a foreign key in another table, using the IDENTITY column paradigm.

    1. I think you’ll have to run another SELECT statement to get the ID. If you’re running the statement as part of PL/SQL you may be able to use a function to get this, but it’s not something I’ve done very often.

      1. Charles Fulghum

        I’m thinking using a returning clause would work.
        insert into (b, c) values (1, 2) returning a into l_a; — where a is the identity column

        Now l_a is available to use and it is the identity value.

  2. I did a test on this 12c method and it does work nice. However, after adding records 1, 2 and 3 with the sequence field auto-magically assigned, I added a fourth record setting the value myself to 5, then added two more records with no id passed, the the new ids came back as 4 and 5 – so I had two records with ids of 5. Sounds like there will be times when you will need to include a trigger to make sure that id’s are unique as well.

    1. That’s a good test. The numbers generated by the sequence are not linked to any table, so the sequence does not know about the record you added manually. A trigger could work. You could also use a Primary Key constraint, or a Unique Constriant, if the values need to be unique.

  3. Charles Fulghum

    For some reason our shop doesn’t like identity clauses so we do things with a sequence as you point out above. But the format can be simplified a little from above now given later Oracle releases:

    CREATE TRIGGER student_bi
    BEFORE INSERT ON student
    FOR EACH ROW
    BEGIN
    :new.student_id := student_id_seq.nextval;
    END;

    We also do it this way, just in case the value might optionally be filled in at insert time:
    :new.student_id := nvl(:new.student_id, student_id_seq.nextval);

  4. Aye Thinzar Oo

    Thank you for your daily article. It is useful for me. I am learning and apply on my workplace oracledb. Because on my workplace, change to MySQL db to Oracle db. It is very pressure on me.

  5. MYSQL AUTO_INCREMENT handles the case when student_id is inserted manually also, the next inserted student_id will always be 1+(maximum value in student_id till now)

    But Oracle doesn’t have this functionality. If by default the next student_id value will be 5 and some one inserts a value 10 in student_id, then the next value (after 10 is inserted) will be 6 only and later on it will throw constraint violation when it reaches 10.

    Is there any alternative for this solution??

    1. I think that’s deliberate. A sequence is independent of the column it is used on. If a sequence is used for an ID, but an ID can also be manually entered, then I think you have a design issue. It should be one method or the other.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.