codescracker


sql

SQL Auto Increment



« Previous Tutorial Next Tutorial »


SQL AUTO INCREMENT allows a unique number that is to be generated automatically every time when the new record is inserted into the table.

Note - The SQL AUTO INCREMENT is mainly used in the primary field.

SQL AUTO INCREMENT Example

Here is the general form of the AUTO INCREMENT in SQL The following SQL statement defines the column "Student_ID" to be an auto-increment primary key field in the table "Students":

For MySQL Database

CREATE TABLE Students
(
Student_ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Student_ID)
)

MySQL uses the AUTO_INCREMENT keyword to perform an SQL auto-increment feature.

By default, the starting value for the AUTO_INCREMENT is 1, and it will increment by 1 for each time when the new record is created.

To let the AUTO_INCREMENT sequence start with another value then use the following SQL statement:

ALTER TABLE Students AUTO_INCREMENT=100

To insert a new record into the table "Students", we will NOT have to specify a value for the column "Student_ID" (a unique value will be added automatically). Let's look at the following SQL Statement:

INSERT INTO Students (FirstName,LastName)
VALUES ('Richard','Jones')

The above SQL statement will insert a new record into the table "Students". The column "Student_ID" will be assigned a unique value. The column "FirstName" will be set to "Richard" and the column "LastName" will be set to "Jones"

For SQL Server

The following SQL statement defines the column "Student_ID" to be an auto-increment primary key field in the table "Students":

CREATE TABLE Students
(
Student_ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS SQL Server uses the IDENTITY keyword to perform the SQL auto-increment feature

To specify that the column "Student_ID" should start at value 10 and increment by 5, then change it to IDENTITY(10,5).

For MS Access Database

The following SQL statement defines the column "Student_ID" to be an auto-increment primary key field in the table "Students"

CREATE TABLE Students
(
Student_ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS Access database uses the AUTOINCREMENT keyword to perform the SQL auto-increment feature.

To specify that the column "Student_ID" should start at value 10 and increment by 5, then change the AUTOINCREMENT to AUTOINCREMENT(10,5).

For Oracle

In Oracle, you will have to create an auto-increment field with the sequence object (this object generates a number sequence)

Use the following CREATE SEQUENCE syntax :

CREATE SEQUENCE seq_student
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

Here, The code above creates a sequence object called seq_student, that starts with 1 and will increment by 1.

It will also cache up to 10 values for the performance. The cache option specifies that, how many sequence values will be stored in memory for faster access

Now to insert a new record into the table "Students", we will have to use the function nextval (this function retrieves the next value from seq_student sequence)

Let's look at the following SQL Statement:

INSERT INTO Students (Student_ID,FirstName,LastName)
VALUES (seq_student.nextval,'Richard','Jones')

The above SQL statement would insert a new record into the table "Students". The column "Student_ID" will be assigned the next number from the seq_student sequence. The column "FirstName" will be set to "Richard" and the column "LastName" will be set to "Jones"


« Previous Tutorial Next Tutorial »



Tools
Calculator

Quick Links
Signup - Login - Give Online Test