MySQL AUTO_INCREMENT

The AUTO_INCREMENT command is used in the SQL language to specify that a numeric column with a primary key (PRIMARY KEY) will be incremented automatically each time a record is inserted into it.
 

 

Syntax:

The SQL query below is a practical example of the auto-increment command:

CREATE TABLE `table_name` (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  [...]
);

By default, the auto-increment starts at the value “1” and will increment by one for each new record. It is possible to change the initial value with the following SQL query:

ALTER TABLE `table_name` AUTO_INCREMENT=100;

In the example above, the initial value for this increment will be 100.
 

Example :

Let’s take the example of a table that will list products. The four columns will be respectively: the id of the product (which will be incremented automatically), the name of the product, the category, stock available, and price.
 

 
Here is the SQL query to create this table and add 2 dummy products:

CREATE TABLE `product` (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  category VARCHAR(50),
  stock INT,
  price INT,
);
-- Add 2 products without defining a value for `id`.
INSERT INTO `product` (`name`, `category`, `stock`, `price`) VALUES ('Keyboard', 'computer', 37, 40);
INSERT INTO `product` (`name`, `category`, `stock`, `price`) VALUES ('Mouse', 'computer', 17, 30);

After running these queries, the table will contain the following data:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|   1  | Keyboard | computer   |  37   | 40      |
|   2  | Mouse    | computer   |  17   | 30      |
+------+----------+------------+-------+---------+

It is possible to see in this table that the “id” column has indeed been incremented without having to worry about the value to be indicated.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More