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.
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:
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
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)
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.
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)
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
Top 5 Courses to learn SQL in depth
50 Microsoft SQL Server Interview Questions with answers
12 Database Index Interview Questions with Answers
50 Microsoft SQL Server Interview Questions with answers
12 Database Index Interview Questions with Answers
2 comments :
Nice Article !
This is my pleasure to read your article.
Really this will help to people of SQL Server Community.
I have also prepared one article about, How to Alphanumeric Sequence Number in SQL Server 2012.
You can also visit my article, your comments and reviews are most welcome.
http://www.dbrnd.com/2016/02/sql-server-2012-create-sequence-object-to-generate-alphanumeric-sequence-number/
@Anvesh, thanks for reading my article. you too have great post
Post a Comment