SQL Auto Increment.
Auto Increment of a column value in the table
- Sometimes we want to generate a unique key for a column in a table.
- Almost all RDBMS provides the ability to define a column of integer type that automatically creates new value for new rows in the table.
- AUTO_INCREMENT column must be indexed. This means that the column must be included as part of a PRIMARY KEY, UNIQUE index or CONSTRAINT definition
- Auto increment specified slightly differently as regards the individual RDBMS.
MySQL
In MySQL RDBMS you can enter AUTO_INCREMENT at the end of selected column.
CREATE TABLE customer (
CUSTOMER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(30) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL VARCHAR(30) NULL
)
CREATE TABLE customer (
CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(30) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL VARCHAR(30) NULL,
PRIMARY KEY (CUSTOMER_ID)
)
CREATE TABLE customer (
CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(30) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL VARCHAR(30) NULL,
CONSTRAINT pk_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
)
The starting value for AUTO_INCREMENT is default 1, and it will increment by 1 for each new row.
SQL Server
In SQL Server RDMS you must enter IDENTITY keyword as part of the specification of the selected column.
CREATE TABLE customer (
CUSTOMER_ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME VARCHAR(30) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL VARCHAR(30) NULL
)
In this example the starting value for IDENTITY is 1, and it will increment by 1 for each new row. A specification of IDENTITY(10,10) gives a start value of 10, and will increment by 10 for each new row.
PostgreSQL
In PostgreSQL RDMS you can enter SERIAL as type of the selected column.
CREATE TABLE customer (
CUSTOMER_ID SERIAL PRIMARY KEY,
FIRST_NAME VARCHAR(30) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL VARCHAR(30) NULL
)
The SERIAL column data will then increase by 1 for each row you insert into the table.
Oracle
In Oracle RDMS you must do it in a two step order.
- You must create an autonumber field by creating a SEQUENCE.
A SEQUENCE is an object in Oracle that is used to generate a number sequence.
ORACLE SEQUENCE example:
CREATE SEQUENCE customer_seq MINVALUE 1 START WITH 1 INCREMENT BY 2 CACHE 20;
This will creates a sequence object called customer_seq, that starts with 1 and will increment by 1. It will also cache up to 20 values for performance.
-
The next step is when you insert row into the table and use customer_seq.nextval to get new values from the sequence.
ORACLE insert example:
INSERT INTO customer (CUSTOMER_ID,FIRST_NAME,LAST_NAME) VALUES (customer_seq.nextval,'Ricard','Moon')