The SQL AUTO INCREMENT keyword allows a unique number to be generated automatically whenever a new record is inserted into a table.
AUTO INCREMENT
This is often the primary key field that we would like to create automatically every time a new record is inserted.
The following SQL statement defines the “customerID” column to be an auto-increment primary key field in the “customer” table:
CREATE TABLE customer ( customerID int NOT NULL AUTO_INCREMENT, companyName varchar(255) NOT NULL, name varchar(255), companyAge int, PRIMARY KEY (customerID) );
AUTO_INCREMENT
How can we define an initial value for increment in MySQL? To let the AUTO_INCREMENT sequence start with another value, we can use the following SQL statement:
ALTER TABLE customers AUTO_INCREMENT = 100;
So once the auto-increment is set for the column we don’t have to specify a value for the “customerID” column.
INSERT INTO customers (companyName, name, companyAge) VALUES ('Thomas','Monsen', 12);
The following SQL Server statement defines the “customerID” column to be an auto-increment primary key field in the “customer” table:
CREATE TABLE customer ( customerID int IDENTITY(1,1) PRIMARY KEY, companyName varchar(255) NOT NULL, name varchar(255), companyAge int );
IDENTITY
How can we define an initial value for increment in MS SQL Server? To specify that the “customerID” column should start at value 10 and increment by 5, change it to IDENTITY(10,5). So on insert of first record it will set “customerID” to 10 and on insert of second record it will set “customerID” to 15 So once the auto-increment is set for the column we don’t have to specify a value for the “customerID” column.
IDENTITY(10,5)
The following SQL statement for MS Access defines the “customerID” column to be an auto-increment primary key field in the “customer” table:
CREATE TABLE customer ( customerID AUTOINCREMENT PRIMARY KEY, companyName varchar(255) NOT NULL, name varchar(255), companyAge int );
AUTOINCREMENT
How can we define an initial value for increment in MS Access? To specify that the “customerID” column should start at value 10 and increment by 5, change it to AUTOINCREMENT(10,5). So on insert of first record it will set “customerID” to 10 and on insert of second record it will set “customerID” to 15
AUTOINCREMENT(10,5)
In Oracle, the code is a little bit more tricky and different. In Oracle, you will have to create an auto-increment field with the sequence object (this object generates a number sequence).
How can we define an initial value for increment in Oracle? Use the CREATE SEQUENCE syntax:
CREATE SEQUENCE
CREATE SEQUENCE seq_customer MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
The above code creates a sequence object called seq_customer, which starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
seq_customer
To insert a new record into the “customer” table, we will have to use the nextval function.
nextval
INSERT INTO customers (customerID, companyName, name, companyAge) VALUES (seq_customer.nextval, 'Thomas','Monsen', 12);