SQL AUTO INCREMENT Field

SQL AUTO INCREMENT Field


The SQL AUTO INCREMENT field is used for auto generating number with user defined values for particular field whenever a new row is being inserted into a table.

If we need to create a column with unique value, we define that column as AUTO INCREMENT field.



Sql auto increment constraint used to auto increment in sql, sql auto generate id, alter table auto_increment, primary key autoincrement, sql insert auto increment, sql server identity increment, and increment value by 1 in select.

Syntax for MySQL

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

MySQL database uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

AUTO_INCREMENT keyword will start value from 1 and it will increment by 1 for each new inserted record. If you want to change the value sequence of AUTO_INCREMENT with user defined value, use the following SQL statement:

ALTER TABLE Books AUTO_INCREMENT = 50;

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Programming','Suresh Babu', 175.5);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Hacking','Suresh Babu', 225);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

After executing above query, the "Books" table look like this:

BookId BookName AuthorName BookPrice
50 Sql Programming Suresh Babu 175.5
51 Sql Hacking Suresh Babu 225

Syntax for MICROSOFT SQL SERVER

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID INT IDENTITY(1,1) PRIMARY KEY,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

SQL SERVER database uses the IDENTITY keyword to perform an auto-increment feature.

In the above example IDENTITY keyword will start value from 1 and it will increment by 1 for each new inserted record.

If you want to specify that the "BookID" field should start at value 200 and increment by 10, change it to IDENTITY(200,10).

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Complete Reference','Siva Kumar', 150);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Administrator','Varshini Kutty', 250);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Pl Sql Quick Book','Bala Murugan', 20);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

Let's assume the IDENTITY will start "200" and increment by "10", then the "Books" table look like this:

BookId BookName AuthorName BookPrice
200 Sql Complete Reference Siva Kumar 150
210 Sql Administrator Varshini Kutty 250
220 Pl Sql Quick Book Bala Murugan 200

Syntax for MICROSOFT ACCESS

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID Integer PRIMARY KEY AUTOINCREMENT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

Microsoft Access database uses the AUTOINCREMENT keyword to perform an auto-increment feature.

In the above example AUTOINCREMENT keyword will start value from 1 and it will increment by 1 for each new inserted record.

If you want to specify that the "BookID" field should start at value 500 and increment by 2, change the AUTOINCREMENT to AUTOINCREMENT(500, 2)..

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Database Reference','Haris Karthik', 350);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Quick Command','Azagu Varshith', 300);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

Let's assume the AUTOINCREMENT will start "500" and increment by "2", then the "Books" table look like this:

BookId BookName AuthorName BookPrice
500 Sql Database Reference Haris Karthik 350
502 Sql Quick Command Azagu Varshith 300


Sql auto increment by sql increment counter, sql insert with auto increment, create table auto increment sql, drop auto increment column, sql change column to auto increment, and auto generate primary key sql.

SQL Auto Increment Fill Gaps

The main key column in SQL Server databases is frequently set to auto-increment. The IDENTITY constraint on the primary key column is used to do this. The IDENTITY column receives the beginning position and increment step as inputs.

The value of the IDENTITY column is then incremented by the pre-defined step, which is normally a number, whenever a new record is inserted. When a record is deleted, the value of the IDENTITY column for that record is likewise removed.

When a new record is added, the IDENTITY column's value is increased from the previous figure in the column. The value that was initially utilised by the now-deleted record cannot be reused. An error will be issued if you try to provide a value for the IDENTITY column.

If the SET IDENTITY INSERT flag is set to ON, any random value can be inserted into the IDENTITY column, as long as it doesn't exist previously.

Example 1: In this tutorial, I'll show you how to fill in a missing value in the IDENTITY column using an instance.

Let's start with some dummy data. On this new database, we'll run our sample queries.

CREATE DATABASE School
GO

USE School
GO

CREATE TABLE Students
(
   Id INT PRIMARY KEY IDENTITY(2,2),
   StudentName VARCHAR (50),
   StudentAge INT
)
GO

INSERT INTO Students VALUES ('Sally', 25 )
INSERT INTO Students VALUES ('Edward', 32 )
INSERT INTO Students VALUES ('Jon', 24 )
INSERT INTO Students VALUES ('Scot', 21)
INSERT INTO Students VALUES ('Ben', 33 )

We build a fictional database called "School" in the script. After that, we run the script that constructs the "Students" table. The table has three columns: Id, StudentName, and StudentAge, as you can see from the design.

The IDENTITY constraint is applied to the Id column, which is the main key column. IDENTITY's seed and step values are both set to 2. This indicates that the Id value for the first record in the "Students" database will be 2, and the value will be incremented by 2 for each consecutive record. Finally, we add 5 records to the Students table at random.

If you select all of the records from the Students table, the Id column will now have a sequence of numbers that begin at 2 and increase by 2 in each row. Run the script that follows:

SELECT * FROM Students

The first record has an id value of 2, whereas the fifth has a value of 10.

Let's say one of the pupils leaves the school and we want to remove his record from the system. A simple DELETE sentence will suffice. Let's get rid of Jon's record:

DELETE FROM Students WHERE StudentName = 'Jon'

If you use the SELECT command to look at all the records in the Students table again, you will get the following result:

SELECT * FROM Students

You can see from the output that record of the student “Jon” with Id 6 has been deleted.

Now let’s try to insert a record of a new student and see what Id it gets:

INSERT INTO Students VALUES ('Jane', 27 )

The preceding script adds a new record to the Students table for a 27-year-old student named "Jane."

To examine the Id assigned to Jane, use the SELECT query to extract all records from the Students table, as seen below:

SELECT * FROM Students

Jane was given the Id 12 instead of the Id 6 that Jon had vacated, as you can see from the result. The reason for this behaviour is that the IDENTITY column provides a value to a new record by adding the step to the previous maximum value in the column rather than filling the column's empty values. Jane is given the number 12 because the previous highest value in the Id column was 10, and the step is two.

This behavior can be correct depending on the business rules of the application being developed. For example, a school may have a restriction that a student's Id cannot be allocated to a new student even if the student quits the school. On the other hand, a school may reassign a student's Id to a new student when that student departs the institution.

Manually Insert Record with specific ID value

In the latter instance, one option is to manually enter the new student's Id value.

Let's try adding a new student record and manually setting the Id column to 6 as seen below:

INSERT INTO Students VALUES (6, 'Nick', 22 )

The preceding script adds a new student named "Nick" to the students table, with an Id of 6 and an age of 22. When you try to run the above script, you'll get an error that looks like this:

Msg 8101, Level 16, State 1, Line 26

Only when a column list is utilised and IDENTITY INSERT is ON may an explicit value for the identity column in table 'Students' be supplied.

The issue simply states that we cannot manually insert any data since the IDENTITY INSERT flag for the Id column is disabled. Another thing to keep in mind is that while putting data into the IDENTITY column, we must also give the column names.

If we simply supply the column names as in the error message above, we get the following results:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

We get this error message.

Msg 544, Level 16, State 1, Line 35

When IDENTITY_INSERT is set to OFF, it is impossible to insert explicit values for the identity column in the table 'Students.'

To manually insert a new value into the Id column, we must first enable the IDENTITY_INSERT_flag:

SET IDENTITY_INSERT Students ON;

The SET statement, followed by the flag name and the table name, is used to turn on the IDENTIT_INSERT flag.

If we try to insert the record of the student "Nick" with Id 6 again, no error message will appear. Run the following command once more:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

For inserting a record, you can see that we have also supplied the names of the columns.

To see if our new record has been inserted, use the SELECT command to fetch all records from the students table. The following records will be returned by the SELECT command.

SELECT * FROM Students
You can see from the output that record of a new student named “Nick”, aged 22, and Id 6 has been inserted to the Students table.

Try Inserting Duplicate Values

If we try to insert duplicate values as follows:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

We will get this error, since ID is the primary key for the table which has to be a unique value.

Msg 2627, Level 14, State 1, Line 35

Violation of PRIMARY KEY constraint 'PK__Students__3214EC071AD8EC3F'. Cannot insert duplicate key in object 'dbo.Students'. The duplicate key value is (6).

The statement has been terminated.

Note, that if the ID column was not a Primary Key we would be able to insert duplicate records.

Turn IDENTITY INSERT off

When you turn on INDENTITY_INSERT, it stays on for the duration of the session (the time the query window is open). You can now insert as many records as you want after this is established. Also, this only applies to the session in which this is turned on, so if you start another query window, you'll have to turn it on again.

You would issue the following statement to switch off this option for the session.

SET IDENTITY_INSERT Students OFF;

Example 2: Let's take the table considered on the previous page and insert into it 3 rows.

CREATE TABLE test_Identity (
     id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY  (id)
    );
    INSERT INTO test_Identity VALUES(),(),();

    SELECT * FROM test_Identity;

Output:

Id
1
2
3

If we remove the last row, the numbering will begin with 4 instead of 3. That is, the counter's most recent value is saved and used for further row additions:

DELETE FROM test_Identity WHERE id=3;

INSERT INTO test_Identity VALUES();

SELECT * FROM test_Identity;

Output:

Id
1
2
4

There is a question: " Whether is it possible to make numbering proceeding from last available value? "

Apart from a question about what it is necessary for, the answer is - possible. But this value of the counter should be set manually. So,

DELETE FROM test_Identity WHERE id=4;

ALTER TABLE test_Identity AUTO_INCREMENT = 3;

INSERT INTO test_Identity VALUES(),(),();

SELECT * FROM test_Identity;
Id
1
2
3
4
5

SQL Auto Increment Reset 1

Using the ALTER TABLE statement in MySQL, you may always reset the next value assigned by the AUTO INCREMENT.

if you want new data to begin at 1 after clearing all data from a table.

Syntax:

The ALTER TABLE statement in MySQL is used to reset the AUTO INCREMENT column:

ALTER TABLE table_name AUTO_INCREMENT = value;
  • table_name - The name of the table where you want to reset the AUTO_INCREMENT column.
  • value - In the AUTO_INCREMENT column, the next value will be utilised.

TIP: Because a table can only have one AUTO INCREMENT field, when resetting the next value in the series, you just have to give the table name (not the field name) in the ALTER TABLE statement.

Important: If the table is empty, only run this command. Insert errors will occur if this is executed on a populated table.

ALWAYS BACKUP before running bulk action commands.

DBCC CHECKIDENT ( Name_Of_Table, RESEED, 0 )

Example: Let's have a look at how to reset the next number associated to an AUTO_INCREMENT column in a MySQL table.

For example, if we had a suppliers table that was defined as follows:

CREATE TABLE suppliers
( supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

The following ALTER TABLE statement could be used to reset the next value in the sequence for the supplier id column (the AUTO_ INCREMENT field in the suppliers table):

ALTER TABLE suppliers AUTO_INCREMENT = 1;

For the supplier id column in the suppliers table, this example would set the next value in the AUTO_INCREMENT field (i.e. the next item in the sequence) to 1. The supplier id column in the newly produced record will now be set to 1 when a new record is placed into the suppliers database.


SQL Auto Increment Skipping Number

A failed insert query is frequently the reason of auto increment ignoring values. If someone has been deleting rows from the MySQL table, these gaps between values may appear.

If the gaps aren't caused by deleted rows, they're most likely the result of unsuccessful insert statements. Keep in mind that changing id=id on duplicate keys will not solve the problem; nevertheless, there are other options.

The good news is that this is perfectly typical behavior caused by MySQL "reserving" auto increment data. It could happen for a variety of reasons, but in my situation, it was caused by a "unique" index on another column, which resulted in a duplicate key issue:

Error Code: 1022. Can't write; duplicate key in table 'unique_name'

The main key column will be incremented even if an insert query fails with the above notice since a row already exists in the table. Insert ignore... or on duplicate key will not help — so what are our options?

You don't have to do anything because auto increment values don't have to be consecutive; they only have to be unique. However, it could be an indication that your queries aren't optimized, and optiminzig your queries is recommendable.

Will you loose data?

When I initially saw unexpected gaps in the primary key values in one of my database tables, the first thing I wondered was if I was losing data, and I even spent a few hours examining the behaviour.

Missing auto increment values aren't always a sign that a query failed to insert data; they could just indicate that the data was already in the table. Although this is still a failure, it is one that was predicted.

When using InnoDB, people have tried to circumvent it by using on duplicate key update id=id, but this will not prohibit the auto increment value from incrementing.

One approach to avoid this is to execute a select query first to see whether the row already exists. However, there would be a tiny time gap between the select and insert statements for someone to insert the data, thus it would not totally solve the problem, and it could sometimes lead to even greater difficulties.

Will you run out of keys?

Another thing that someone might be concerned about is running out of primary key numbers. You can get over 2 billion values with an INT (2,147,483,647).

Of however, if you're making something like an HTTP request logger, you'll run out of keys more quickly. But the issue here isn't the key; it's that you're employing the incorrect tool for the task.

The auto increment value is nearly never reset, and the request log is rarely cleaned. This implies that you will eventually run out of values. Typically, only entries older than a year are erased.

The usage of BIGINT as a workaround would at the very least postpone the problem for years.

You wouldn't run out of BIGINT if every single human on the earth visited your site a million times in a single second!

It will almost certainly cause the internet to break, but you'll still have more than nine quintillion remaining before you run out.

The maximum size of an unsigned BIGINT is 9,223,372,036,854,775,807; if everyone in the world pressed a million buttons at the same time, you'd only be down approximately 7,000,000,000,000,000. (seven quadrillion)

What happens when you run out?

This may be dependent on the database, however testing with a smaller datatype is actually quite simple. So, let's use TINYINT to make a test table.

Of course, with INT, there's always the risk of running out of numbers. However, it is still improbable that that will happen. In fact, if your database only had a single VARCHAR column and every single row contained a field with 255 characters, your table would have expanded to 68 Gigabytes; as you can see, it is unlikely but not impossible.

When you omit numbers, you won't take up nearly as much space before you run out of keys. This is why you may want to take action before it occurs. There are only 127 unsigned keys allowed in a TINYINT. To see what happens, you can make a table:

create table `test_table` (`id` tinyint not null auto_increment, `whatever` varchar(255) not null, 
primary key (`id`), 
unique key `whatever` (`whatever`)) engine=InnoDB auto_increment=127;

Note the auto_increment=127 part; this tells MySQL that the auto increment should begin from this number. If we then try to execute a couple of insert queries:

insert into test_table (whatever) values ('Doing it');
insert into test_table (whatever) values ('Not doing it');

It will result in an error like:

Duplicate entry '127' for key 'test_table.PRIMARY'

And until we alter the datatype, the table will be unable to add any more records.

alter table test_table modify id int;

Or, if you think you will need it, use BIGINT:

alter table test_table modify id bigint;

SQL Auto Increment 2

In Postgres, changing the auto-increment begin and increment values requires using the ALTER keyword and creating a custom SEQUENCE as a distinct, single-row table, which you'll then put into your employee table.

First, create a custom SEQUENCE using:

CREATE SEQUENCE sequencename
start 2
increment 2;

Note: To prevent conflict, ensure sure this sequence name is unique (that is, it does not share the same name as any other tables, sequences, indexes, views, or foreign tables in your existing schema).

Exmaple 1: In our example, we want item_number to start at 50 and go up by five with each new value. The code for that would look like:

CREATE SEQUENCE item_number_pk
start 50
increment 5;

Then, in the inventory table we built previously, you insert this record and update the item number value to this new item SEQUENCE, as shown:

INSERT INTO Inventory
(item_number, item_type, item_category, item_name)
VALUES
(nextval('item_number_pk'), 'Item Type', 'Item Category', 'Item Name');

You'll have to call out that the item number value is each time you add a value to your table (see below):

nextval('sequencename')

If you only want to change the starting value of your IDs (not the increment value), you may save yourself some time by using ALTER SEQUENCE. The basic syntax is as follows:

ALTER SEQUENCE project_id_seq RESTART [value];

For our example, this looks like:

ALTER SEQUENCE Inventory RESTART 50;

Example 2: If you're using MySQL, you can change the auto-increment starting values with ALTER TABLE. Because MySQL does not allow you to adjust the default increment value, you're trapped.

The basic syntax for this looks like:

ALTER TABLE TableName MODIFY COLUMN value DataType AUTO_INCREMENT=50;

Using our inventory table example, your adjustment to the auto-increment start value would be:

ALTER TABLE Inventory MODIFY COLUMN item_number INT AUTO_INCREMENT=50;

After running this code, future item IDs will start at an item_number of 50 and increment by 1.

To change the starting increment value and increment in SQL Server, set your non-default values during table creation. Looking back at our base syntax from the previous section:

CREATE TABLE TableName (
Column1 DataType IDENTITY(starting value, increment by),
Column2 DataType,
);

In our example, where we want item_number to start at 50 and go up by five with each new value, the code would look like:

CREATE TABLE Inventory (
item_number int IDENTITY(100,5) PRIMARY KEY,
item_type varchar(255),
item_category varchar(255),
item_name varchar(255);

SQL Server will battle you if you try to add auto increment to an existing table by altering an existing int column to IDENTITY. You'll get to choose between:

Create a new column with your new auto-incremented primary key, or Remove your existing int column and replace it with a new IDENTITY. To avoid duplicating primary keys in the future, make sure your new IDENTITY starts at +1 the value of the last id in your discarded column in the second situation.

To do this, you’d use the following code:

ALTER TABLE Table DROP COLUMN old_id_column

ALTER TABLE Table ADD new_auto_incremented_volumn INT IDENTITY(starting value, increment value)

With our example, this would look like:

ALTER TABLE Inventory DROP COLUMN old_item_number
ALTER TABLE Inventory ADD item_number INT IDENTITY(50, 5)

Depending on the DBMS you use, if you're following through with our example scenario, your output should look like this table (with the exception of MySQL, which doesn't accept non-default increments of 5). In the next step, we'll add the values listed below.

Retool_Auto_Increment_Table1

SQL Auto Increment After Delete

When you delete a row from the table, the table will automatically increment as if the row had never been deleted. As a result, there is a void in the number sequence. Although this is usually not a problem, you may want to reset the auto increment field.

Our table occasionally loses the most recent entries. Attempt to insert some rows after the rows have been deleted. In many circumstances, a gap in the number_values of the auto_increment column is acceptable. However, it is frequently necessary to reset the auto_increment in order to retain the sequence. There is, however, a solution to the problem.

Example 1: Let's have a look at an example. Assume the table sales data contains the following row elements.

We'll delete the table sales data's last two rows, then create a new row, and finally select * from sales_data. For more information, look at the questions below.

DELETE FROM sales_data WHERE sale_person_id  > 4;

INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Rani",15,5000); 

SELECT * FROM sales_data;

As we can see that since two rows with sale_person_id values ‘5’ and ‘6’ got deleted, the newly inserted row has id ‘7’. To avoid this behaviour, let us write another query, as shown below.

ALTER TABLE sales_data AUTO_INCREMENT=1;

Let us rerun the insert after deleting the rows, and the output will look like this.

As can be seen, the sequence is preserved, and the new row's sale_person_id value was '5' instead of '7.'

Example 2: There are two simple steps in resetting an auto increment field:

1.Find the highest number in the auto increment field

SELECT MAX( `column` ) FROM `table` ;

Replace 'column' with the auto-incrementing column's name. Replace table with the table's name.

2. Reset the auto increment field:

The next step is to take the number returned by the first command and automatically increment it. So double that sum by one and perform the code below:

ALTER TABLE `table` AUTO_INCREMENT = number;

Replace 'number' with the preceding command's result plus one, and table with the table name.

You could use the modify table command to reset the table to 0 if you erased all of the rows.


SQL Auto Increment Alter Table

As you add more rows to the database, the Auto Increment columns automatically rise in value.

Change or set the next value assigned by the AUTO_INCREMENT using the ALTER TABLE statement.

We can create a new column, such as an id, that automatically increments for each record entered into the table.

In this MySQL, we'll construct a new PRIMARY KEY column using the AUTO_INCREMENT column modifier.

Syntax:

To add a new column to MySQL, following is the syntax of the SQL Query:

ALTER TABLE table_name
ADD [COLUMN] new_column_name AUTO_INCREMENT PRIMARY KEY;

Example 1: AUTO_INCREMENT for PRIMARY KEY

For this example, let us consider the following table, students.

To add a new column named id that works as the PRIMARY KEY and auto increments for each insert, use the SQL Query below.

ALTER TABLE students
 ADD id INT AUTO_INCREMENT PRIMARY KEY;

The id column was successfully inserted. Let's look at the contents of the new table.

Example 2: To understand the above concept, create a table with a column. Let us create a table:

create table AlterTableToAddAutoIncrement
    (
    StudentId int
   )

Implement the above syntax to change “StudentId” with AUTOINCREMENT. The query is as follows :

alter table AlterTableToAddAutoIncrement change StudentId StudentId int AUTO_INCREMENT Primary  key;

We've introduced AUTO_INCREMENT to the 'StudentId' column. You may now test whether or not autoincrement is working by inserting entries with no value.

insert into AlterTableToAddAutoIncrement values();

Now you can display all records which will be displayed in sequence of 1, 2, 3, 4, etc. The query is as follows :

select *from AlterTableToAddAutoIncrement;

Output:

StudentId
1
2
3

SQL Auto Increment Constraint

The benefits of having numeric, auto incremented primary keys are numerous, but the most significant are improved query speed and data independence when searching through thousands of entries that may contain constantly changed data elsewhere in the table. Applications can benefit from these speedier and more reliable inquiries if they have a consistent and unique numeric identity.

Databases are used to logically store massive amounts of data. You may have encountered situations when you have problems mentioning a unique number for each record in the table. Due to the manual entry restrictions, this approach is basically impossible. As a result, there is no way to increase the values.

Some of the features of auto-increment are enlisted below:

  • It enables you to construct Primary Keys for data that may lack a unique identification property.
  • At any point in time, the value in auto-increment can be explicitly initialised and updated.
  • It is simple to develop unique record identification.
  • The flexibility to process the gap between each record is simply managed using Auto Increment.
  • The syntax for auto-increment queries in SQL is simple.

1. Setting up Auto Increment:

You can begin by establishing a table with particular attributes such as CustomerID, CustomerName, Age, PhoneNumber, and so on. You must use the IDENTITY keyword in SQL Server to use the auto-increment field.

Syntax

CREATE TABLE TableName (  
Column1 DataType  IDENTITY(starting value, increment by),  
Column2 DataType,   
);  

Example:

1.Basic Table Creation:

Once you've connected to SQL Server, you'll typically begin by CREATING a new table with the field you want to use as your incremented primary key. We'll use the tried and proven id field as an example:

CREATE TABLE books (
  id              INT           NOT NULL,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

The issue is that we have no control over our id field. When creating a new record, we must not only manually enter an id value, but also run a query to ensure that the id value does not already exist (a near-impossibility when dealing with many simultaneous connections).

2. Using Identity and Primary Key Constraints:

The first is PRIMARY KEY, which makes the given column operate as a completely unique index for the table, allowing for faster searching and queries.

While SQL Server permits just one PRIMARY KEY constraint per table, it can be defined for many columns. Individual columns in a multi-column situation may include duplicate, non-unique values, but the PRIMARY KEY constraint assures that every restricted value combination is unique in relation to every other combination.

The IDENTITY constraint is the second piece of the puzzle, which tells SQL Server to automatically increase the numeric value within the given column whenever a new record is INSERTED. While the IDENTITY constraint can accept two parameters, the numeric seed and the increment, these values are often not supplied with the IDENTITY constraint and are instead left as defaults (both default to 1).

With this new information, we can add our two additional constraints to our earlier CREATE TABLE command.

CREATE TABLE books (
  id              INT           NOT NULL    IDENTITY    PRIMARY KEY,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

It's as simple as that. Our books table's id column will now be automatically increased with each INSERT, and the id field will be assured to be unique.


SQL Auto Increment Remove Disable Drop

To disable self-increment on SQL Server, change the self-increment field value manually.

Suppose you've established auto increment on a column in a MySQL table. It must be disabled or removed. You don't want to delete the column, but you don't need auto increment going ahead, so you'd rather manually enter the values in the column.

To disable or remove the auto increment from a column in a MySQL table, just create a new column with the same data type as the original but no auto_increment clause.

SET IDENTITY_INSERT

Allow explicit values ??to be inserted into the identity column of the table.

Syntax:-

SET IDENTITY_INSERT [ database .[ Owner .] ] { Table } { ON | OFF }

Example 1:

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

INSERT INTO products (id,product) VALUES (2,'screwdriver')  //ERROR
set IDENTITY_INSERT ub..products on
INSERT INTO products (id,product) VALUES (2,'screwdriver')//OK
set IDENTITY_INSERT ub..products off
INSERT INTO products (id,product) VALUES (4,'screwdriver')  //ERROR
INSERT INTO products (product) VALUES ('scrdsafdasdewdriver')

select * from products

Example 2: Let's say if I have customer table with auto increment column "idcustomer" and I would like to remove the auto increment property or feature, I can use below syntax.

ALTER TABLE customer 
modify idcustomer int(11);

The idcustomer column was already int(11) , I used the statement to modify the column so Auto increment can be dropped.


SQL Auto Increment Columns Into Existing Table

As you add more rows to the table, the value of the Auto Increment columns will automatically increase.

Syntax:

Here is the syntax to add auto-increment column in the existing table in SQL:

ALTER TABLE Table_Name

AUTO_INCREMENT = starting value;

Here’s the SQL statement to add AUTO INCREMENT constraint to id column.

ALTER TABLE sales
MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

You can specify the starting value as you want. If you do not specify the starting value will be ‘1’ and will increment by ‘1’ after every record.

Example: How to Add Auto Increment Column

Here are the steps to add auto increment column in MySQL. Let’s say you have the following sales(id, amount) table.

create table sales(id int, amount int);

insert into sales(id,amount) 
values(1, 100),(4,300),(6,400);

select * from sales;

Output:

+------+--------+
| id   | amount |
+------+--------+
|    1 |    100 |
|    4 |    300 |
|    6 |    400 |
+------+--------+

Now, we will modify the id column to be auto increment, using ALTER TABLE.

Next we will add a couple of rows in sales table.

insert into sales(amount) values(150),(250);
select * from sales;

Output:

+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  4 |    300 |
|  6 |    400 |
|  7 |    150 |
|  8 |    250 |
+----+--------+

As you can see, the MySQL has automatically increased and populated id column with values 7 and 8.


SQL Auto Increment Last Insert Id

LAST_INSERT_ID() (no parameters) returns the first automatically produced value for an AUTO_INCREMENT column that was successfully added as a result of the most recently performed INSERT statement. If no rows are successfully inserted, the value of LAST INSERT ID() remains unaltered.

If an argument is passed to LAST_INSERT_ID(), the value of the expression is returned, and the next call to LAST_INSERT_ID() returns the same value. The value will also be provided to the client, and the mysql insert id function can obtain it.

If your column is AUTO_INCREMENT, you can use the last_insert_id() method. In MySQL, this function returns the ID of the most recently inserted record.

After an INSERT statement, there are several options for getting the value of a column that uses AUTO_INCREMENT. Use a SELECT query with the LAST_INSERT_ID() method to get the value just after an INSERT.

Getting the last_inserted_id of an auto-incremented field in a MySQL table using the INFORMATION SCHEMA.

Note: LAST_INSERT_ID() returns 0 if no rows were successfully added.

If all rows in the INSERT or UPDATE statement were successful, the value of LAST_INSERT_ID() will remain consistent across all editions.

If you create an AUTO_INCREMENT value with one statement and then use LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column, you'll get an error. In the second statement, the value of LAST_INSERT_ID() will not change.

The result of LAST_INSERT_ID() is undefined if the previous statement failed. If the statement is rolled back due to an error in transactional tables, the value of LAST_INSERT_ID() is left undefined. The value of LAST_INSERT_ID() is not restored before the transaction in manual ROLLBACK; it remains as it was at the time of the ROLLBACK.

The value of LAST_INSERT_ID() changes in the body of a stored routine (procedure or function) or a trigger in the same way that it does outside the body of these objects. The effect of a stored procedure or trigger on the value of LAST_INSERT_ID() as seen by the following statements varies according to the type of routine:

The new value of LAST_INSERT_ID() will be noticed by statements that follow the procedure call if a stored procedure performs statements that modify its value.

When a stored function or trigger that changes a value ends, the value is recovered.

Syntax:

SELECT LAST_INSERT_ID();

Example 1:

Step 1: let us create a table. The query to create a table is as follows

create table LastInsertedRow
  (
  Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  UserName varchar(20),  
  UserAge int
  );

Step 2: Insert some records in the table using insert command.

The query is as follows

insert into LastInsertedRow(UserName,UserAge) values('John',23);
insert into LastInsertedRow(UserName,UserAge) values('Carol',24);
insert into LastInsertedRow(UserName,UserAge) values('Bob',24);
insert into LastInsertedRow(UserName,UserAge) values('Larry',26);
insert into LastInsertedRow(UserName,UserAge) values('Maxwell',27);
insert into LastInsertedRow(UserName,UserAge) values('David',22);

Step 3: Display all records from the table using select statement.

select *from LastInsertedRow;

Output:

+----+----------+---------+
| Id | UserName | UserAge |
+----+----------+---------+
|  1 | John     |      23 |
|  2 | Carol    |      24 |
|  3 | Bob      |      24 |
|  4 | Larry    |      26 |
|  5 | Maxwell  |      27 |
|  6 | David    |      22 |
----------------+---------+

Step 4: Here is the query to get the last inserted row

select last_insert_id();

Output:

last_insert_id
6

Step 5: If you want the entire row, then use the following query

select * from LastInsertedRow where Id=(SELECT LAST_INSERT_ID());

Output:

Id UserName USerAge
6 David 22

Example 2:

CREATE TABLE t (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  f VARCHAR(1)) 
ENGINE = InnoDB;

INSERT INTO t(f) VALUES('a');

SELECT LAST_INSERT_ID();

Output:

LAST_INSERT_ID()
1
INSERT INTO t(f) VALUES('b');

INSERT INTO t(f) VALUES('c');

SELECT LAST_INSERT_ID();

Output:

LAST_INSERT_ID()
3

INSERT INTO t(f) VALUES('d'),('e'); SELECT LAST_INSERT_ID();

Output:

LAST_INSERT_ID()
4
SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
SELECT LAST_INSERT_ID(12);
LAST_INSERT_ID(12)
12
SELECT LAST_INSERT_ID();

Output:

LAST_INSERT_ID()
12
INSERT INTO t(f) VALUES('f');

SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
6
SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
+----+------+
SELECT LAST_INSERT_ID(12);
LAST_INSERT_ID(12)
12
INSERT INTO t(f) VALUES('g');
SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
|  7 | g    |
+----+------+

SQL Auto Increment Starting Value

Change or set the next value assigned by the AUTO_INCREMENT using the ALTER TABLE statement.

In SQL Server, you must utilize the IDENTITY keyword to use the auto increment field.

Syntax:

CREATE TABLE TableName (
Column1 DataType  IDENTITY(starting value, increment by),
Column2 DataType, 
);

Example: For your better understanding, I will consider the following table:

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int IDENTITY(1,1) PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);

The beginning value for IDENTITY in the previous example is 1, and it should increase by 1 for each new record entered. You may specify these values if you so want. You must also use the INSERT query in the following manner to insert values in the aforementioned table:

INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Abhay','25','9876543210');

Here, if you observe, I have not mentioned the CustomerID column, as the ID will be automatically generated. So, if you see insert 4 more values using the below queries:

INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Sonal','22','9812313210');
  
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Anuj','19','9956413210');
  
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Mona','24','9876543911');
  
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Sanjay','31','9657154310');

Output:

CustomerID	CustomerName	Age	PhoneNumber
1	             Abhay	        25	9876543210
2	             Sonal	        22	9812313210
3	             Anuj	        19	9956413210
4	             Mona	        24	9876543911
5	             Sanjay	        31	9657154310

2. Auto increment in mysql

In MySQL, you must use the AUTO_INCREMENT keyword to use the auto increment column. AUTO_INCREMENT starts at 1 by default and increments by 1 for each new record.

Syntax:

CREATE TABLE TableName (
Column1 DataType  AUTO_INCREMENT,
Column2 DataType, 
);

ALTER TABLE table_name AUTO_INCREMENT = start_value;

Paramters:

table_name - The table name for which you want to adjust the AUTO_INCREMENT setting. Because a table in MySQL can only have one AUTO_INCREMENT column, you just need to specify the sequence's table name. The name of the column containing the AUTO_INCREMENT value does not need to be specified.

start_value - The next value in the sequence to assign in the AUTO_INCREMENT column.

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int AUTO_INCREMENT PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);

If you wish to start the AUTO_INCREMENT value by any other number, then you can use the keyword in the following way:

Example:

ALTER TABLE Customers AUTO_INCREMENT=50;

Using the INSERT statement, similar to SQL Server, you can INSERT values into the table. When you insert values, you'll get the same result as the table above.