MySQL auto_increment

MySQL contains a feature for primary key columns called auto increment. A table column that has the AUTO_INCREMENT attribute causes a MySQL database to automatically store unique, strictly increasing integers in that column. Consider the following table definition:

DROP TABLE IF EXISTS customer;

CREATE TABLE IF NOT EXISTS customer (
    customer_id INT NOT NULL AUTO_INCREMENT,
    given_name VARCHAR(45) NOT NULL,
    family_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (customer_id)
) ENGINE = InnoDB;

Because of the AUTO_INCREMENT attribute on the customer_id column, when we write an INSERT statement, we don’t have to give a value for the customer_id column.

INSERT INTO customer
(given_name, family_name)
VALUES
('Adrian', 'Baker'),
('Gianna', 'Santos'),
('Kevin', 'Wong');

SELECT * FROM customer;

customer_id	given_name	family_name
1	Adrian	Baker
2	Gianna	Santos
3	Kevin	Wong

AUTO_INCREMENT is a nice feature that can save a developer some work. However, it is a mixed blessing. Consider trying to write an INSERT statement for a child table of customer, such as an order table.

DROP TABLE IF EXISTS `order`;

CREATE TABLE IF NOT EXISTS `order` (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    ship_date DATE NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_customer_id
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE = InnoDB;

When we write the INSERT statement, how do we know what foreign key value to use? In other words, the INSERT statement for the customer table does not include primary key values, so if we want to make an order for Gianna Santos, we have to count or guess which primary key to use in the INSERT statement for the order table.

INSERT INTO `order`
(customer_id, order_date, ship_date)
VALUES
(2, '2022-07-15', '2022-07-18');

One possibility is to explicitly write the primary key values in the INSERT statement. Even though the primary key column has the AUTO_INCREMENT attribute, a developer can still explicitly write the primary keys. Then the developer will not have to count or guess the value to use for the foreign key column.

INSERT INTO customer
(given_name, family_name)
VALUES
(1, 'Adrian', 'Baker'),
(2, 'Gianna', 'Santos'),
(3, 'Kevin', 'Wong');

SELECT * FROM customer;

customer_id	given_name	family_name
1	Adrian	Baker
2	Gianna	Santos
3	Kevin	Wong

Instead of counting or guessing a foreign key value, another possibility is to use a SELECT query to find the correct foreign key value.

INSERT INTO `order`
(customer_id, order_date, ship_date)
VALUES
((SELECT customer_id FROM customer
    WHERE given_name = 'Gianna' AND family_name = 'Santos'),
    '2022-07-15', '2022-07-18');

SELECT order_id, customer_id, given_name, family_name,
    order_date, ship_date
FROM `order` AS o INNER JOIN customer AS c
    ON c.customer_id = o.customer_id;

order_id	customer_id	given_name	family_name	order_date	ship_date
1	2	Gianna	Santos	2022-07-15	2022-07-18

One of the strange features of AUTO_INCREMENT is that even if we delete all the rows from a table, the next sequential value for an auto incremented column does not reset.

DELETE FROM `order`;
DELETE FROM customer;

INSERT INTO customer
(given_name, family_name)
VALUES
('Adrian', 'Baker'),
('Gianna', 'Santos'),
('Kevin', 'Wong');

SELECT * FROM customer;

customer_id	given_name	family_name
4	Adrian	Baker
5	Gianna	Santos
6	Kevin	Wong

We can use an ALTER command to reset the AUTO_INCREMENT value.

DELETE FROM `order`;
DELETE FROM customer;

ALTER TABLE `order` AUTO_INCREMENT = 1;
ALTER TABLE customer AUTO_INCREMENT = 1;

INSERT INTO customer
(given_name, family_name)
VALUES
('Adrian', 'Baker'),
('Gianna', 'Santos'),
('Kevin', 'Wong');

SELECT * FROM customer;

customer_id	given_name	family_name
1	Adrian	Baker
2	Gianna	Santos
3	Kevin	Wong

We may be interested to see if there are any gaps between primary key values. We can use a self join to find gaps if any exist.

DELETE FROM customer
WHERE customer_id = 2;

SELECT a.customer_id,
    MIN(b.customer_id) - a.customer_id AS gap
FROM customer AS a INNER JOIN customer AS b
    ON a.customer_id < b.customer_id
GROUP BY a.customer_id
HAVING gap > 1
ORDER BY a.customer_id;

customer_id	gap
1	2