SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

SQL Auto Increment

As we all know, In SQL for unique identification, we assign a column with the primary key. But in some tables, we find difficult to differentiate a column as a unique identifier to set the primary key.

So, to over come this problem, SQL server came up with a solution of providing unique keys to every record. Auto increment feature in SQL works for assigning the primary key for a numerical value for every record automatically as it is inserted.

This Auto increment feature is supported by all the databases like SQL Server, MS Access, Oracle, MySQL and PostgreSQL.

Auto Increment feature can be implemented in various servers.

  1. SQL server
  2. MySQL
  3. PostgreSQL
  4. MS Access.
  5. Oracle

1.SQL auto Increment:

In the SQL Server the Syntax goes as below:

IDENTITY (Start value, increment value)  

Parameters:

  • Start value: We should mention the starting value, which w would like to use.
  • Increment value: We should mention a value by which we want to increment the key for the next records.

Example:

Let’s create an employee table having Employee name, ID, Salary, Age as the columns for the table. Using the Auto – increment feature, we will assign the employee IDs for every Employee Automatically.

For using this feature we should provide the code with the starting value and the increment value. So, for this example we give the starting value as 3201 and increment value as 1.

We need to mention Auto increment feature during the creation of the table.

create table employee ( employee_ID int IDENTITY (3201, 1) PRIMARY KEY, employee_name varchar(20), employee_Salary int, employee_age int);

Now we need to insert the records into the table. This is done using the INSERT command, a Data Manipulation Language command.

insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dhruv’, 100000, 20) ; 
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Krish’, 150000, 19) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dany’, 130000, 21) ;

To view all the records,

select * from employee;
Employee_IDEmployee_nameEmployeee_SalaryEmployee_Age
3201Dhruv10000020
3202Krish15000019
3203Dany13000021

2. MySQL Auto Increment:

In MySQL Server also the auto increment feature works the same. The variation arises at the syntax during the implementation.

In here, the default starting value and default increment value will be 1 and 1 respectively.

Example:

First, we will create a student table having Student_name, Student_ID, Student_marks as the fields.

If we mention the Auto increment feature during the table creation, this activates the default values.

create table student ( Student_ID int AUTO_INCREMENT PRIMARY KEY, Student_name varchar(20), Student_marks int , Student_age int) ;

Now, we should insert the records into the tables using INSERT command. 

insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dhruv’, 95, 20) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Krish’, 80, 19) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dany’, 97, 21) ;
select * from Student;

This command will display all the records from the table.

Student_IDStudent_nameStudent_marksStudent_age
1Dhruv9520
2Krish8019
3Dany9721

NOTE POINTS:

  • For customizing the starting value, we can use the ALTER TABLE command.

Example:

ALTER TABLE Student AUTO_INCREMENT = customized value;

The customized value over here is the new starting value for the table student.

  • For changing the Auto increment interval value from 1 to any other customized value/new value, we write the following command using auto_increment_increment.

Example:

mysql>

SET @@ auto_increment_increment = new_value_for_interval;

3.PostgreSQL Auto Increment:

In PostgreSQL, we use SERIAL keyword for implementing the auto increment feature.

Example:

At first, we need to create a student table using the SERIAL Keyword. Consider Student_ID, Student_marks, Student_Name, Student_age as the fields in the student table.

create table student ( student_ID int SERIAL PRIMARY KEY, Student_Name varchar(20), Student_marks int, Student_age int) ;

Now we should insert the rows into it without mentioning the Student_ID value, as we allotted the Auto increment feature to the Student_ID field of the student table.

insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dhruv’, 95, 20) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Krish’, 80, 19) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dany’, 97, 21) ;
select * from student;

This command will display all the records from the table.

Student_IDStudent_nameStudent_marksStudent_age
1Dhruv9520
2Krish8019
3Dany9721

4. MS Access Auto Increment:

The Auto increment feature is also implemented in MS Access using the AUTOINCREMENT keyword. The default starting value and the default increment value is 1 and 1 respectively.

Example:

Create a table with table name as Employee having employee_ID, employee_Name, employee_Salary, employee_Age as columns.

create table Employee ( employee_ID number AUTOINCREMENT PRIMARY KEY, employee_Name varchar(20), employee_Salary number, employee_Age number);

Now, we should insert the rows into the table. There is no need of specifying the employee_ID value while inserting as here we are using the auto increment feature.

insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dhruv’, 100000, 20) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Krish’, 150000, 19) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dany’, 130000, 21) ;
select * from Employee;
Employee_IDEmployee_nameEmployeee_SalaryEmployee_Age
1Dhruv10000020
2Krish15000019
3Dany13000021

As we can observe in the above table, the employee_ID column is having ID’s starting from 1 and incremented by 1 for each row by default. We can change the starting value and increment value also. The syntax for implementing this is,

AUTOINCREMENT ( starting value, increment value)

5.Oracle Auto Increment:

We should make the Auto increment field along with the sequence object which then generates a number sequence.

The syntax in the oracle server works as below:

minivalue 1
start with 1
increment by 1
cache 10;

Example:

create students_seq 
minvalue 10
start with 1001
increment by 1
cache: 20;

cache: 20;

To insert the records into the students table, we will use the nextval function. The main use of this function is that, we can retrieve the post value from the students_seq sequence.

insert into Students( student_ID, student_Name) values (students_seq.nextval, “Anshu”);
insert into Students( student_ID, student_Name) values (students_seq.nextval, “Dhruv”);
insert into Students( student_ID, student_Name) values (students_seq.nextval, “Daksh”);

Output:

Student_IDStudent_Name
1001Anshu
1002Dhruv
1003Daksh