Shows the syntax and comments on how to create tables whose IDs increment with each new record inserted. Code taken from: http://www.w3schools.com/sql/sql_autoincrement.asp

auto_increment.sql

--Syntax for MySQL:
--Keyword: AUTO_INCREMENT

CREATE TABLE Persons
(
ID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255),
PRIMARY KEY (ID)
)

--The default starting value is 1, to make the autoincrement start with another value,use ALTER:

ALTER TABLE Persons AUTO_INCREMENT=100

--To insert a new record, we don't need to specify a value for the ID column:

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

------------------------------------------------------------------------------
--Syntax for SQL Server:
--Keyword: IDENTITY(starting_value,increment)

(
ID INT IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
)

--To insert a new value in the table, we don't need to specify a value for the ID column.

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

------------------------------------------------------------------------------

--Syntax for Access
--Keyword: AUTOINCREMENT

CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

--The default starting value is 1. The default increment is 1 
--To change the default starting value and increment, we can use AUTOINCREMENT(10,5)
--To insert a new value in the table, we don't need to specify a value for the ID column.

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

---------------------------------------------------------------------------------
--Syntax for Oracle
--We have to create an auto-increment field with the sequence object (this object generates a number sequence).

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

--To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')