SQL AUTO INCREMENT Field

SqlAUTO INCREMENT Field

Auto-increment will generate a unique number when the new record is inserted into the table.

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 "ID" column in the "Persons" table as the Auto-increment primary key field:

CREATE TABLEPersons (IDint  not NULLAuto_increment,lastnamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255),PRIMARY KEY(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 "ID" column (a unique value is added automatically):

INSERT  into Persons (firstname,lastname) VALUES ('Lars','monsen')

The SQL statement above inserts a new record in the "Persons" table. The "ID" column is given a unique value. The "FirstName" column is set to "Lars" and the "LastName" column is set to "Monsen".

Syntax for SQL Server

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

CREATE TABLEPersons (IDint IDENTITY(1,1)PRIMARY KEY, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255))

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

In the above instance, the start value of the IDENTITY is 1, and each new record is incremented by 1.

tip: To specify the ID column to start at 10 and increment by 5, change the identity to identity (10,5).

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

INSERT  into Persons (firstname,lastname) VALUES ('Lars','monsen')

The SQL statement above inserts a new record in the "Persons" table. The "ID" column is given a unique value. The "FirstName" column is set to "Lars" and the "LastName" column is set to "Monsen".

Syntax for Access

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

CREATE TABLEPersons (IDInteger PRIMARY KEYAutoincrement,lastnamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(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.

tip: To specify the "ID" column to start at 10 and increment by 5, change the AutoIncrement to AutoIncrement (10,5).

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

INSERT  into Persons (firstname,lastname) VALUES ('Lars','monsen')

The SQL statement above inserts a new record in the "Persons" table. The "ID" column is given a unique value. The "FirstName" column is set to "Lars" and the "LastName" column is set to "Monsen".

Syntax for Oracle

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

You must create a auto-increment field from the Sequence object, which generates a sequence of numbers.

Please use the CREATE SEQUENCE syntax below:

CREATE1with 1 by110 

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 (id,firstname,lastname) VALUES (Seq_person.nextval,'Lars','monsen')

The SQL statement above inserts a new record in the "Persons" table. The ID column is assigned the next number from the Seq_person sequence. The "FirstName" column is set to "Lars" and the "LastName" column is set to "Monsen".

Add the self-increment syntax to an already existing colume:

ALTER TABLE table_name Change column_name column_name data_type (size) constraint_name auto_increment;

Like what:

ALTER TABLE INT  One  not NULL Auto_increment;

SQL AUTO INCREMENT Field