How to use MySQL auto-increment

Auto-increment is a very important attribute of MySQL. When a table requires a numeric field that will increment automatically to generate a sequential number then the auto-increment attribute is used for that field. The auto-increment field can be assigned as the primary key or unique key for a table if requires. This field can’t store NULL value. So, when the auto-increment attribute is set for any field of a table then NOT NULL constraint will set automatically for that field. When new records need to insert into a table that contains the auto-increment field, the user doesn’t need to provide any value for that field. How this attribute works in MySQL tables is shown in this article.

Features of the auto-increment field:

• The first record of this field always starts from 1 by default and incremented by 1 when a new record inserts.
• If the user specifies any particular numeric value without NULL for this field at the time of insertion that is not in the sequential order then an error message will be generated by MySQL.
• If any value of this field is updated by another value that already exists in the table then MySQL will generate an error message.
• If the user deletes the last records from the table then what will be the new sequential number depends on the table’s engine. InnoDB table never generates the previously generated number when a new records insert, but the MyISAM table generates the last sequential number that is removed from the table.
• LAST_INSERT_ID() function is used to retrieve the value of the number that is generated in the last insertion.

Syntax:

CREATE TABLE table1
(
field1 datatype AUTO_INCREMENT [PRIMARY KEY],
field2 datatype [ NULL | NOT NULL ],
...
fieldn datatype [ NULL | NOT NULL ],
);

Here, field1 is defined as auto-increment field and the datatype of this field can be any numeric datatype like INT or BIGINT. It is not mandatory to define the auto-increment field as the PRIMARY KEY. But It can be used as a PRIMARY KEY to create a relationship between two tables.

Prerequisite:

Run the following SQL commands to create a database named ‘newdb’ and select the database for creating tables with auto-increment attribute.

CREATE DATABASE newdb;
use newdb;

Create a table with auto-increment:

Run the following CREATE statement to create a table named students where id field will be created with auto-increment attribute and set as a primary key. Next, two types of INSERT statements will be executed. In the first INSERT statement, no field name is mentioned in the insert query and you have to provide all field values of the table for this type of insertion. Here, the NULL value is used for id field. In the second INSERT statement, all fields except the auto-increment field are mentioned in the insert query because it will be generated automatically. Next, the SELECT statement is executed to display the content of students table.

CREATE TABLE students (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
batch SMALLINT NOT NULL,
semester SMALLINT NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO students VALUES
(NULL, 'Masrafi', 41, 9);

INSERT INTO students(name, batch, semester) VALUES
('Sakib', 43, 7);

SELECT * FROM students;

You can set the value of the auto-increment field manually but you have to maintain the sequential order. You can’t set any value lower than the last inserted value or equal to any existing value. The following first INSERT statement will work properly because the last inserted value was 2. The second INSERT statement will generate an error because the value 2 already exists in the table.

INSERT INTO students VALUES
(4, 'Robel', 41, 9);

INSERT INTO students VALUES
(2, 'Manzarul', 41, 9);

Create a table with auto-increment and UNSIGNED ZEROFILL:

It mentioned earlier that, the auto-increment field starts from 1 by default. But if you use UNSIGNED ZEROFILL attribute with auto-increment field and set the length of the number then the number will be generated with leading zero based on the length. The following CREATE statement will create a table named teachers where auto-increment and UNSIGNED ZEROFILL attributes are set for tch_id field and the length of the field is set to 4. Next, some data will be inserted into the table by INSERT statement and the SELECT statement will display all content of the table.

CREATE TABLE teachers (
tch_id MEDIUMINT(4) UNSIGNED ZEROFILL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
department VARCHAR(10) NOT NULL,
PRIMARY KEY (tch_id)
);

INSERT INTO teachers VALUES
(NULL, 'Maria', 'CSE'),
(NULL, 'Janifer', 'BBA'),
(NULL, 'Micheal', 'ENG');
SELECT * FROM teachers;

Here, It is shown that 0001, 0002 and 0003 are generated as tch_id values.

Now, if you delete the last record and insert a new record then a new number more the deleted tch_id value will be generated as new tch_id.

DELETE FROM teachers WHERE tch_id = 3;

INSERT INTO teachers VALUES
(NULL, 'Mahmuda', 'CSE');

SELECT * FROM teachers;

Resetting auto-increment field:

If all records are deleted from the teachers table that contains the auto-increment field then the new value of tch_id will be generated after the last inserted value. After running the following SQL statements, it will be shown that the newly generated tch_id is 0005 because the last inserted value was 0004.

DELETE FROM teachers;

INSERT INTO teachers VALUES
(NULL, 'Lucy', 'EEE');

SELECT * FROM teachers;

If you want to reset the table and start the value from 1 again then you have to execute TRUNCATE statement instead of the DELETE statement. This is shown in the following three statements.

TRUNCATE table teachers;

INSERT INTO teachers VALUES
(NULL, 'Lucy', 'EEE');

SELECT * FROM teachers;

You will get the following output after running the statements.

If you want to change the default value of the auto-increment fields then you have to run the ALTER statement with starting auto-increment value. Next, insert a record and check the value of the auto-increment field. Here, the starting value will be set to 15.

TRUNCATE table teachers;

ALTER TABLE teachers AUTO_INCREMENT = 15;

INSERT INTO teachers VALUES
(NULL, 'Lucy', 'EEE');

SELECT * FROM teachers;

The following output will appear after running the above SQL statements.

Conclusion:

The purposes of the auto-increment attribute are explained properly by using sample tables in this article to help the MySQL user to understand the uses of this attribute.

from Linux Hint https://ift.tt/2RWhZJC