Prev Next
- AUTOINCREMENT is a keyword in SQL which is used to increment value of a field automatically while inserting new records in a table.
- AUTOINCREMENT keyword is used generally for primary keys to identify each row by unique value
- AUTOINCREMENT keyword can be used for integer field only. Syntax for SQL AUTOINCREMENT is given below.
SQL Syntax for AUTOINCREMENT keyword:
| Syntax for AUTOINCREMENT keyword | CREATE TABLE table_name (column_name1 INTEGER AUTOINCREMENT, column_name2 data_type(size), column_name3 data_type(size), etc…); |
Example for how to use the SQL AUTOINCREMENT keyword:
- Please execute below query in SQL to create a table with AUTOINCREMENT keyword.
CREATE TABLE student
(Student_ID int AUTOINCREMENT,
Student_name varchar(255),
City varchar(255),
Marks int);
- Now, a new table called “student” is created and the field “Student_ID” is an auto increment field which will be incremented by 1 for every new data inserted in the table and it starts from 1.
- Now, please insert some records using insert SQL as below and see how data are inserted into this newly created table.
INSERT INTO student (Student_name, City, Marks)
VALUES (‘Raju’, ‘Chennai’, 80);
INSERT INTO student (Student_name, City, Marks)
VALUES (‘Mani’, ‘Delhi’, 85);
INSERT INTO student (Student_name, City, Marks)
VALUES (‘Thiyagarajan’, ‘Vizag’, 70);
INSERT INTO student (Student_name, City, Marks)
VALUES (‘Surendren’, ‘Pune’, 96);
Output:
Note:
As above, whenever a new record is inserted using insert statement, Student_ID will be auto incremented on table.