How to Create Auto Incremented Identity Column in SQL Server, MySQL, and Oracle? Example

Automatic incremented ID, Sequence, or Identity columns are those columns in any table whose value is automatically incremented by database based upon predefined rule. Almost all databases e.g. Microsoft SQL Server, MySQL, Oracle or Sybase supports auto-incremented identity columns but in different ways like Oracle provides a SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server up to 2008 version provides IDENTITY() functions for a similar purpose. Sybase also has IDENTITY function but little different than SQL Server and MySQL uses auto_incremented keyword to make any numeric column auto-incremented.

As first normal form advised about primary keys which is used to uniquely identity row and if there is no natural column or combination of columns exists to act as primary key, mostly database developers use auto-incremented surrogate keys which is used to uniquely identify each row. 

In this SQL tutorial, we will see how to generate an auto-incremented ID column in Microsoft SQL Server, Oracle 11g, MySQL, and Sybase ASE Server. 

By the way, this SQL article is the continuation of my earlier post on SQL and database like the difference between truncate and delete in SQL and Finding the second highest salary in MySQL and SQL Server. If you haven't got a chance to read them then I suggest they are worth looking at.

Auto incremented Id or sequence in SQL Server

SQL Server has IDENTITY(seed, incremental value) function which can be used along with any column to make that auto-incremented id column. It takes two parameters one is the seed which is starting value and the other is the incremental value which is used to generate the next number. default is IDENTITY(1,1) which generated sequential ids like 1, 2, 3, 4, etc. 

Once you make any column as an IDENTITY column you don't need to provide value for that and it will be automatically incremented and inserted by SQL Server. Here is the SQL Server query to generate IDENTITY columns:

DROP TABLE employee

CREATE TABLE employee (emp_id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL, emp_name varchar(50) NULL, emp_phone bigint NULL)

INSERT INTO employee VALUES('Jack', 98434343)
INSERT INTO employee VALUES('Jill', 78434343)
INSERT INTO employee VALUES('Mack', 68434343)

SELECT * FROM employee

emp_id  emp_name        emp_phone
1       Jack            98434343
2       Jill            78434343
3       Mack            68434343

SQL Server also support SEQUENCE object, which can also be used to create automatically incremented ids but its not completely automatic and while inserting data you need to call sequence.next or something similar to populate next value.

Auto incremented Id or sequence in Sybase

Sybase Adaptive Server or ASE also supports IDENTITY column but with slightly different way than SQL Server 2005 or 2008 e.g. it doesn't have any IDENTITY() function instead it have IDENTITY keyword which can be applied to any column while creating table using "create table" statement or "select into" statement as shown below:

CREATE TABLE employee  (emp_id numeric(5,0) identity, emp_name varchar(50) NULL, emp_phone bigint NULL)

Here the maximum value of identity is 10^5 -1 or 9999. Some important points related to the IDENTITY column in Sybase is :

1) One table can only have an IDENTITY column.

2) Similar to SQL Server, Sybase Adaptive Server also generates a value of IDENTITY column automatically

3) Each row has a unique value for the identity column which can be used to an identity that row.

4) IDENTITY columns can not be updated and do not allows nulls in Sybase database.

By the way, you can also create an IDENTITY column by modifying existing table also.

Auto incremented Id or sequence in MySQL

MySQL database is completely different that SQL Server or Sybase Database but it also supports concept of Identity column by keyword AUTO_INCREMENT. AUTO_INCREMENT can be used to uniquely identify a row in a table and  can be used to create primary key.

mysql> CREATE TABLE customers (cust_id INT PRIMARY KEY AUTO_INCREMENT, cust_name VARCHAR (20), cust_phone INT);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO customers(cust_name, cust_phone) VALUES ("Mitchell", 668332211);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO customers(cust_name, cust_phone) VALUES ("Rose", 98322365);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM customers;
+---------+-----------+------------+
| cust_id | cust_name | cust_phone |
+---------+-----------+------------+
|       1 | Mitchell  |  668332211 |
|       2 | Rose      |   98322365 |
+---------+-----------+------------+
2 rows IN SET (0.00 sec)

Important points about AUTO INCREMENTED in MySQL

1) If you don't specify value of AUTO_INCREMENT than MySQL server automatically insert values as shown above.

2) Make sure you use big enough data type to hold value of automatically generated ids. e.g. if you use TINYINT than maximum value of automatic id is 127.

3) You can get the last auto incremented id in MySQL by using function LAST_INSERT_ID() .

Auto incremented Id or sequence in Oracle database

In Oracle 10g database you can use SEQUENCE to generate automatically increment unique values. In order to use sequences you first need to create the SEQUENCE object in database and then while inserting data into database you need to use SEQUENCE.NEXTVAL to populate identity column.

CREATE TABLE Orders (order_id number(1), amount number(20))
INSERT INTO Orders(id_sequence.NEXTVAL, 200)
INSERT INTO Orders(id_sequence.NEXTVAL, 400)

That’s all on How to create auto-incremented ID, identity column or sequence in Oracle, SQL Server, MySQL, and Sybase database. This is one of the fundamental concepts in SQL and it's always good to know how to create identity columns in the respective database you are working.

Related Database and SQL tutorials from Javarevisited Blog

Difference between correlated and noncorrelated subquery in SQL

10 example of SELECT command in SQL

How to copy table schema from another table in SQL

How to find duplicate records in a table using SQL query

How to join three tables in SQL query

10 MySQL command Examples

Top 5 Courses to learn SQL in depth
50 Microsoft SQL Server Interview Questions with answers
12 Database Index Interview Questions with Answers