SQL AUTO INCREMENT Field

AUTO INCREMENT Field

We typically want to automatically create a value for the primary key field each time a new record is inserted.

We can create a auto-increment field in the table.

Syntax for MySQL

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255) ,PRIMARY KEY (P_Id))

MySQL uses the Auto_increment keyword to perform auto-increment tasks.

By default, the start value of Auto_increment is 1, and each new record is incremented by 1.

To have the auto_increment sequence start with a different value, use the following SQL syntax:

ALTER TABLE Persons auto_increment=100

To insert a new record in the "Persons" table, we do not have to specify a value for the "p_id" column (a unique value is added automatically):

INSERT into Persons (firstname,lastname) VALUES (' Bill ', ' Gates ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".

Syntax for SQL Server

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255) )

MS SQL uses the IDENTITY keyword to perform auto-increment tasks.

By default, the start value of the IDENTITY is 1, and each new record is incremented by 1.

To specify that the "p_id" column starts at 20 and increments by 10, change the identity to identity (20,10)

To insert a new record in the "Persons" table, we do not have to specify a value for the "p_id" column (a unique value is added automatically):

INSERT into Persons (firstname,lastname) VALUES (' Bill ', ' Gates ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".

Syntax for Access

The following SQL statement defines the "p_id" column in the "Persons" table as the Auto-increment primary key:

CREATE TABLE Persons ( P_Id int PRIMARY KEY AUTOINCREMENT, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255) )

MS Access uses the AutoIncrement keyword to perform auto-increment tasks.

By default, the start value of AutoIncrement is 1, and each new record is incremented by 1.

To specify the "p_id" column to start at 20 and increment by 10, change the AutoIncrement to AutoIncrement (20,10)

To insert a new record in the "Persons" table, we do not have to specify a value for the "p_id" column (a unique value is added automatically):

INSERT into Persons (firstname,lastname) VALUES (' Bill ', ' Gates ')

The SQL statement above inserts a new record in the "Persons" table. "P_ID" will be given a unique value. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".

Syntax for Oracle

In Oracle, the code is a little bit more complicated.

You must create a auto-increment field (the object generates a sequence of numbers) through sequence.

Please use the CREATE SEQUENCE syntax below:

CREATE SEQUENCE Seq_personminvalue 1START with 1INCREMENT by 1CACHE 10

The above code creates a sequence object named Seq_person, which starts with 1 and increments by 1. The object caches 10 values to improve performance. The CACHE option specifies how many sequence values to store in order to increase the speed of access.

To insert a new record in the "Persons" table, we must use the Nextval function (the function fetches the next value from the Seq_person sequence):

INSERT into Persons (p_id,firstname,lastname) VALUES (seq_person.nextval, ' Lars ', ' Monsen ')

The SQL statement above inserts a new record in the "Persons" table. The assignment of "p_id" is the next number from the Seq_person sequence. "FirstName" will be set to "Bill", "LastName" column will be set to "Gates".

SQL AUTO INCREMENT Field