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
MySQL database is completely different that SQL Server or Sybase Databasebut 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
Source:http://javarevisited.blogspot.com/2012/12/create-auto-incremented-identity-column-oracle-sequence-sql-server-mysql-sybase.html
Tidak ada komentar:
Posting Komentar