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

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,Oracleor 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.

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 TABLEemployee

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

INSERT INTOemployee VALUES('Jack', 98434343)
INSERT INTOemployee VALUES('Jill', 78434343)
INSERT INTOemployee 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.

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

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

Auto incremented Id, identity colum Sequence Oracle Sybase SQL Server and MySQL databaseMySQL 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 TABLEcustomers (cust_id INT PRIMARY KEY AUTO_INCREMENT, cust_name VARCHAR (20), cust_phone INT);
Query OK, 0rows affected (0.09sec)

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

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

mysql> SELECT * FROMcustomers;
+---------+-----------+------------+
| 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 databaseand then while inserting data into database you need to use SEQUENCE.NEXTVAL to populate identity column.

CREATE TABLEOrders (order_id number(1), amount number(20))
INSERT INTOOrders(id_sequence.NEXTVAL, 200)
INSERT INTOOrders(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