SQL Auto Increment - sql - sql tutorial - learn sql




  • AUTO INCREMENT fields are used for auto generating values for particular column whenever new row is being inserted.
  • Very often the primary key of a table needs to be created automatically; we define that field as AUTO INCREMENT field.
  • AUTO_INCREMENT is used in MySQL to create a numerical primary key value for each additional row of data.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax:

  • The syntax for AUTO_INCREMENT is as follows:
CREATE TABLE TABLE_NAME 
(PRIMARY_KEY_COLUMN INT NOT NULL AUTO_INCREMENT 
... 
PRIMARY KEY (PRIMARY_KEY_COLUMN));
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax for MySQL:

  • The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Student" table:
CREATE TABLE Student (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
  • MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
  • By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
  • To let the AUTO_INCREMENT sequence starts with another value, use the following SQL statement:
 ALTER TABLE Student AUTO_INCREMENT=100;
  • To insert a new record into the " Student " table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):
INSERT INTO Student (FirstName,LastName)
VALUES ('Lars','Monsen');
  • The SQL statement above would insert a new record into the " Student " table. The "ID" column would be assigned a unique value.
  • The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Syntax for Oracle:

  • In Oracle, the code is a little bit trickier.
  • You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
  • Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
  • The code above creates a sequence object called seq_person, that 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.
  • To insert a new record into the "Student" table, we will have to use the next Val function (this function retrieves the next value from seq_person sequence).
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');
  • The SQL statement above would insert a new record into the "Persons" table.
  • The "ID" column would be assigned the next number from the seq_person sequence.
  • The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

MS ACCESS: AUTO INCREMENT:

  • column_name data_type constraint AUTOINCREMENT;

Example: MS AUTO INCREMENT

CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)
  • The default starting value of AUTOINCREMENT is 1 and will increment by 1 for each record. To modify it alter the value as in example below.
  EmployeeID INT PRIMARY KEY AUTOINCREMENT(1000,2)

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to SQL Auto Increment