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.