Primary keys in databases uniquely identify each record, ensuring data integrity and efficient retrieval. AUTO_INCREMENT, a feature in MySQL, automatically assigns unique numeric identifiers to new rows, simplifying data management.

Understanding these concepts is crucial for designing robust database schemas that optimize data storage and retrieval. This introduction briefly covers the importance of primary keys, AUTO_INCREMENT functionality, and their relevance in database design.

Solutions for AUTO_INCREMENT IDs in MySQL

So we have some common solutions with some built-in functions and some with procedures(Triggers) that we will create. Let’s first understand them.

1. AUTO_INCREMENT: This is the built-in property used to solve the above issue. It will automatically add the new ID after the last one, and we don’t have to give anything related to the primary key in the Insert statement.
2. Trigger: This approach will require the understanding of triggers. It will trigger the procedure as soon as the insert query is run. We will create a Trigger which will run before actual insertion in the database so that we can count the id and then we can append the id by ourselves.

1. AUTO_INCREMENT with Create Statement

In this, we will look to Create a table and with it, we will mention the “AUTO_INCREMENT” As discussed it will manage the id by itself.

Syntax:

CREATE TABLE table_name (

id INT AUTO_INCREMENT PRIMARY KEY,

–Other columns

);

Example

CREATE TABLE AutoIncrement (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Now insert some values into it to see whether the id is generating by itself

INSERT INTO AutoIncrement (name, age) 
VALUES ("John", 20),
       ("Mark", 35),
       ("Johnson", 40);

Output:

DELETE FROM AutoIncrement WHERE id = 3;
SELECT * FROM AutoIncrement;

INSERT INTO AutoIncrement(name, age) 
VALUES  ("Watson",20);

SELECT * from AutoIncrement  

We can also change the AUTO_INCREMENT starting value other than 1 by using giving AUTO_INCREMENT = value property.

CREATE TABLE AutoIncrementAlt (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO AutoIncrementAlt (id, name, age) 
VALUES (1, "Watson", 20),
       (2, "Mark", 35),
       (4, "Johnson", 40);

SELECT * FROM AutoIncrementAlt;

ALTER TABLE AutoIncrementAlt
MODIFY COLUMN id INT AUTO_INCREMENT;
 ALTER TABLE AutoIncrementAlt AUTO_INCREMENT = 5;

INSERT INTO AutoIncrementAlt (id, name, age) 
VALUES (3, "JOHN", 20);

SELECT * FROM AutoIncrementAlt;

CREATE TABLE AutoIncrementWithTrigger (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

DELIMITER //
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON AutoIncrementWithTrigger
FOR EACH ROW
BEGIN
    DECLARE last_id INT;
    SET last_id = (SELECT COALESCE(MAX(id), 0) FROM AutoIncrementWithTrigger);
    SET NEW.id = last_id + 1;
END;
//
DELIMITER ;

INSERT INTO AutoIncrementWithTrigger (name, age) 
VALUES  ("John", 20),
        ("Mark", 35),
        ("Johnson", 40);