Our today’s topic of discussion is Auto Increment in SQL. This property are mostly used by the ‘Primary Key’ in the table. As the name suggests,It allows a random/unique number to generate every-time a record is inserted in the table.Without any further delay let us jump to today’s topic , and enjoy the essence of it .
Syntax:
column_name data_type constraint AUTOINCREMENT;
Syntax for MySQL:
AutoIncrement has different structures for different versions of SQL. Let us look at the mySQL version of AutoIncrement Field.
Example: Auto Increment is generally used by the Primary key, Auto Increment Function is to Increment the value without giving any command to it. Following example mentioned below is the example for Auto Increment in MySQL. Attributes like First name , last name , and cooking-id needs to be updated. ID being unique is the primary key for the table.Auto increment value is attached with it , and need not to be updated with each data entry.
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
cookingId int,
PRIMARY KEY (ID)
);
ID= 1, 2, 3, 4, 5, 6... [auto increment]
Note:Initial value attached with Auto Increment is -‘1’.
Ex:The following example mentioned below do not have to specify the value for ‘id’. ID is auto incremented. Every time we add a value in the table a unique value is updated to the ID column.
INSERT INTO Persons (FirstName,cookingID)
VALUES ('Harsh','01');
Syntax for SQL Server
Auto-Increment Functionality is same in all forms of SQL , but the representation changes. Let us look at the auto increment field used in “SQL Server”. SQL server uses the keyword- “IDENTITY” for auto_increment.
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
countryID int
);
In the example mentioned above IDENTITY(1,1) defines that starting value of ID is -‘1’ and increments the value by-‘1’.
Ex: IDENTITY(5,10)- Value is defined to -‘5′ , and is auto incremented by -’10’.
IDENTITY(2,3)- Value is defined to -‘2’, and is auto incremented by-‘3’.
INSERT INTO Persons (FirstName,LastName)
VALUES ('Harsh','Singh');
ID value is not updated in the following statement. auto-increment( IDENTITY) values is attached to the ID attribute.
Syntax for Access
Ms-Access is other form of writing of SQL language. MS Access uses the –” AUTOINCREMENT” keyword to use the auto-increment feature.
CREATE TABLE Persons (
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
cookingID int
);
Ex: AUTOINCREMENT(10,20) -’10’ is the initial value assigned to the ID . ‘ID’ updates its value by ’20’. [10,30,50,70,......]
INSERT INTO Persons (FirstName,LastName)
VALUES ('Harsh','Singh');
ID value is not updated in the following statement. auto-increment( IDENTITY) values is attached to the ID attribute.
Syntax for Oracle
Oracle is other form of writing of SQL language. Oracle uses the ” CREATE SEQUENCE “ method to deal with auto-increment feature.
CREATE SEQUENCE padhle_oracle MINVALUE 2 START WITH 2 INCREMENT BY 2 CACHE 20;
The code above mentioned creates a sequence object (“padhle_oracle”).
MINVALUE-“The minimum value assigned to the field” (ID-2)
START WITH-” The initial value assigned to the attribute(ID-2)
INCREMENT BY-Updates the value by the margin of ‘2’ ( ID-2,4,6,8.....)
In order to update the table we need to execute the following command:
INSERT INTO Persons (ID,FirstName,LastName) VALUES (padhle_oracle,'Harsh','Singh');
- “padhle.oracle” sequence object is called by the VALUES statement. Object has all the information relating to the auto-increment feature.