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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×