Tuesday, December 25, 2012

How to create auto incremented identity column in SQL Server, MySQL, Sybase and Oracle ?

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 SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server upto 2008 version provides IDENTITY() functions for 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 column exists to act as primary key, mostly database developer use auto incremented surrogate keys which is used to uniquely identify each row. In this SQL tutorial we will see how to generate auto incremented ID column in Microsoft SQL Server, Oracle 11g, MySQL and Sybase ASE Server. By the way this SQL article is continuation of my earlier post on SQL and database like difference between truncate and delete in SQL and Finding second highest salary in MySQL and SQL Server. If you haven't got chance to read them than I suggest they are worth looking.


Auto incremented Id or sequence in SQL Server

SQL Server have IDENTITY(seed, incremental value) function which can be used along with any column to make that auto incremented id column. It takes two parameter one is seed which is starting value and other is incremental value which is used to generate next number. default is IDENTITY(1,1) which generated sequential ids like 1, 2, 3, 4 etc. Once you make any column as 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 2012 is going to 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 maximum value of identity is 10^5 -1 or 9999. Some important points related to IDENTITY column in Sybase is :
1) One table can only have on IDENTITY column.
2) Similar to SQL Server, Sybase Adaptive Server also generates value of IDENTITY column automatically
3) Each row has unique value for identity column which can be used to 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 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 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 concept in SQL and it's always good to know how to create identity column in respective database you are working.

Related Database and SQL tutorials from Javarevisited Blog

No comments :

Post a Comment