Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Sql Delete Func Curtime ❯

SQL AUTO INCREMENT Field

Auto-increment generates a unique number when a new record is inserted into a table.

AUTO INCREMENT Field

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

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

Syntax for MySQL

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

MySQL uses the AUTO_INCREMENT keyword to perform the auto-increment task.

By default, the starting value of AUTO_INCREMENT is 1, and it increments by 1 for each new record.

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

When inserting a new record into the "Persons" table, we do not need to specify a value for the "ID" column (a unique value will be automatically added):

The above SQL statement will insert a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for SQL Server

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

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

In the above example, the starting value of IDENTITY is 1, and it increments by 1 for each new record.

Tip: To start the "ID" column at 10 and increment by 5, change identity to IDENTITY(10,5).

When inserting a new record into the "Persons" table, we do not need to specify a value for the "ID" column (a unique value will be automatically added):

The above SQL statement will insert a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for Access

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

MS Access uses the AUTOINCREMENT keyword to perform the auto-increment task.

By default, the starting value of AUTOINCREMENT is 1, and it increments by 1 for each new record.

Tip: To start the "ID" column at 10 and increment by 5, change autoincrement to AUTOINCREMENT(10,5).

When inserting a new record into the "Persons" table, we do not need to specify a value for the "ID" column (a unique value will be automatically added):

The above SQL statement will insert a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for Oracle

In Oracle, the code is slightly more complex.

You must create an auto-increment field through a sequence object (which generates a number sequence).

Use the following CREATE SEQUENCE syntax:

The above code creates a sequence object named seq_person, starting at 1 and incrementing by 1. It caches 10 values to improve performance. The cache option specifies how many sequence values to store for faster access.

To insert a new record into the "Persons" table, we must use the nextval function (which retrieves the next value from the seq_person sequence):

The above SQL statement will insert a new record into the "Persons" table. The "ID" column will be assigned the next number from the seq_person sequence. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

❮ Sql Delete Func Curtime ❯