SQL Auto Increment
AUTO INCREMENT Field
When a new record is added to a table, auto-increment enables the creation of a unique integer.
This is frequently the main key field that we want to be generated automatically each time a new record is added.
Syntax for MySQL
The “Personid” column in the “Persons” database is defined as an auto-increment primary key field using the SQL statement that follows:
My SQL / SQL Server / Oracle / MS Access:
------ EXAMPLE MUKAVU -----
MySQL’s auto-increment capability is implemented via the AUTO_INCREMENT keyword.
AUTO_INCREMENT has a beginning value of 1 by default and increases by 1 with each new record.
Use the following SQL query to change the value at which the AUTO_INCREMENT sequence begins:
ALTER TABLE Persons AUTO_INCREMENT=100;
We won’t need to provide a value for the “Personid” column when adding a new entry to the “Persons” database because a unique value will be added automatically:
------ EXAMPLE MUKAVU -----
A new record would be inserted into the “Persons” database with the aforementioned SQL query. A distinct value would be assigned to the “Personid” field. “Lars” would be entered in the “FirstName” field and “Monsen” in the “LastName” column.
Syntax for SQL Server
The “Personid” column in the “Persons” database is defined as an auto-increment primary key field using the SQL statement that follows:
------ EXAMPLE MUKAVU -----
The auto-increment feature of the MS SQL Server is carried out using the IDENTITY keyword.
As seen in the preceding example, IDENTITY has a beginning value of 1 and increases by 1 with each new entry.
Advice: Change “Personid” to IDENTITY(10,5) to indicate that the value should begin at 10 and increase by 5.
We won’t need to provide a value for the “Personid” column when adding a new entry to the “Persons” database because a unique value will be added automatically:
------ EXAMPLE MUKAVU -----
A new record would be inserted into the “Persons” database with the aforementioned SQL query. A distinct value would be assigned to the “Personid” field. “Lars” would be entered in the “FirstName” field and “Monsen” in the “LastName” column.
Syntax for Access
The “Personid” column in the “Persons” database is defined as an auto-increment primary key field using the SQL statement that follows:
------ EXAMPLE MUKAVU -----
The auto-increment capability in MS Access is accomplished by using the AUTOINCREMENT keyword.
AUTOINCREMENT has a beginning value of 1 by default and increases by 1 with each new record.
Advice: Set the autoincrement to AUTOINCREMENT(10,5) to indicate that the “Personid” column should begin at value 10 and increase by 5.
We won’t need to provide a value for the “Personid” column when adding a new entry to the “Persons” database because a unique value will be added automatically:
------ EXAMPLE MUKAVU -----
A new record would be inserted into the “Persons” database with the aforementioned SQL query. A distinct value would be assigned to the “Personid” field. “Lars” would be entered in the “FirstName” field and “Monsen” in the “LastName” column.
Syntax for Oracle
The code in Oracle is a little bit trickier.
The sequence object, which generates a numerical sequence, must be used to build an auto-increment field.
Apply the CREATE SEQUENCE syntax listed below:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
The aforementioned code generates a series object called seq_person, which has a starting value of 1 and increments by 1. For performance, it will also cache a maximum of ten values. The number of sequence values that will be kept in memory for quicker access is specified by the cache option.
The nextval function, which gets the subsequent value from the seq_person sequence, must be used in order to add a new record to the “Persons” table:
INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');
A new record would be inserted into the “Persons” database with the aforementioned SQL query. The subsequent number from the seq_person sequence would be allocated to the “Personid” column. “Lars” would be entered in the “FirstName” field and “Monsen” in the “LastName” column.
