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.

1CREATE 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.

1CREATE TRIGGER student_bi
2BEFORE INSERT ON student
3FOR EACH ROW
4BEGIN
5  SELECT student_id_seq.nextval
6  INTO :new.student_id
7  FROM dual;
8END;

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.

1SELECT student_id, first_name, last_name
2FROM student
3ORDER BY student_id;

Result:

1No 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.

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

Now, let's select the data again.

1SELECT student_id, first_name, last_name
2FROM student
3ORDER 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.

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

And, select from the table again.

1SELECT student_id, first_name, last_name
2FROM student
3ORDER BY student_id;

Result:

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:

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

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

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

We can then SELECT from the table.

1SELECT student_id, first_name, last_name
2FROM student12c
3ORDER BY student_id;

1INSERT INTO student12c (first_name, last_name)
2VALUES ('Joan', 'Colbert');
3
4SELECT student_id, first_name, last_name
5FROM student12c
6ORDER BY student_id;

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!