SQL AUTO INCREMENT Field

Submitted by admin on Saturday, April 16, 2011 - 11:40.

The AUTO INCREMENT generates a new number that increment by 1 from the previous number. This will allow a unique number to be associated in a row when it is inserted into a table.

You can set the AUTO INCREMENT during creation of the table or you can also use ALTER TABLE Statement if you have already created the table.

The following example will create a table named "Users" and add a Primary Key with Auto Increment in ID field.

Syntax for SQL Server

CREATE TABLE Users ( ID int PRIMARY KEY IDENTITY, LastName varchar(30) NOT NULL, FirstName varchar(30), Salary Float, DeptID varchar(255) )

Syntax for MySQL

CREATE TABLE Users ( ID int NOT NULL AUTO_INCREMENT, Firstname text, Lastname text, Salary Float, DeptID int, PRIMARY KEY (ID) )

Syntax for MS Access

CREATE TABLE Users ( ID AUTOINCREMENT, Firstname text, Lastname text, Salary Float, DeptID Int, PRIMARY KEY (ID) )

Syntax for Oracle

CREATE SEQUENCE userids MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10

As you can see above, each DBMS has its own syntax. The important here is that you know how each syntax works. Regardless of DBMS that you are using, learning programming is by way of learning every syntax.

To learn on how to insert record to your table visit "INSERT Statement" chapter.

Tags

Add new comment

About text formats
  • 86 views