Auto-increment is a very useful feature that allows a unique number to be generated when a new record is inserted into a table. The DBMS will automatically increment the last number of the table and add it to the auto-incremented column. Unfortunately, different DBMSs use different ways or syntax to do this. In this post, I will show you how to include the auto-increment feature in MySQL, Microsoft SQL Server, and Oracle.

Auto-Increment in MySQL

CREATE TABLE Students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
fullname VARCHAR(80) NOT NULL
);

As you can see from the above code, the auto-increment feature is very simple when it’s applied in MySQL. You only need to add auto_increment as shown in line 2. By default, the auto_increment value starts at 1. If you want the auto_increment to start with value other than 1, you have to set that value using ALTER TABLE:

ALTER TABLE Students AUTO_INCREMENT=2000

Auto-Increment in MS SQL Server

CREATE TABLE Students (
id INT IDENTITY PRIMARY KEY ,
fullname VARCHAR(80) NOT NULL
);

Similar to MySQL, MS SQL Server uses a simple syntax to perform auto-increment feature; i.e. using identity keyword. By default, the starting value for identity is 1. If you want the id column to start at value 2000 and increment by 10, then you need to change the IDENTITY property to IDENTITY(2000,10). Simple, eh?

Auto-Increment in Oracle

In Oracle, the auto-increment feature can be quite tricky, as it requires us to write more lines of code using PL/SQL. Take a look at this:

CREATE TABLE Students
(
  id INT,
  fullname VARCHAR2(80) NOT NULL,
  primary key (id)
);

CREATE SEQUENCE seq_students
start with 1
increment by 1
nomaxvalue;

CREATE OR REPLACE TRIGGER trig_students
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
  SELECT seq_students.nextval into :new.id from dual;
END;
/

To perform the auto-increment feature, Oracle uses both sequence to generate the numbers and trigger to add the incremented number to the destination column. Line 14 to 17 shows how trigger trig_students adds the next available number (that is given by seq_students) into the column id in table Students, before the insertion of a new record on Students table occurs.

If you want the id column to start at value 2000 and increment by 10, you need to change the sequence seq_students to:

CREATE SEQUENCE seq_students
start with 2000
increment by 10
nomaxvalue;

You can also set a maximum value for that particular column by changing the nomaxvalue property to maxvalue 5000, for example.