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 tutorialSyntax:
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 tutorialSyntax for MySQL:
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 tutorialMS ACCESS: AUTO INCREMENT:
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)