AUTO_INCREMENT does not working in MySQL project-CodePudding

I have problem with MySQL Code. I have two tables:

CREATE TABLE IF NOT EXISTS Renovation_Managment_System.Car_Company_Data(
    car_company_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    company_name VARCHAR(50) NOT NULL,
    city VARCHAR(100) NOT NULL,
    post_code VARCHAR(50) NOT NULL,
    num_of_industrial_halls INT UNSIGNED NOT NULL,
    PRIMARY KEY (car_company_id)
    )ENGINE = INNODB
    COLLATE 'utf8_general_ci';

    -- with data like this:
    -- INSERT INTO Renovation_Managment_System.Car_Company_Data VALUES
    -- (NULL,'Volkswagen','Poznan','60-655',2),
    -- (NULL,'Volkswagen','Wrzesnia','60-657',3),

CREATE TABLE IF NOT EXISTS Renovation_Managment_System.Industrial_Hall_Data(
    industrial_hall_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    car_company_id INT UNSIGNED NOT NULL,
    industrial_hall_token VARCHAR(25) NOT NULL,
    area INT UNSIGNED NOT NULL,
    num_of_employees INT UNSIGNED NOT NULL,
    PRIMARY KEY(industrial_hall_id) 
    )ENGINE = INNODB
    COLLATE 'utf8_general_ci';

In car_company_data I have 21 records. Then I want to add to column industrial_hall_token something like this: First letter of company_name First letter of city last two letters of post_code. For this procedure i have code which looks like this:

INSERT INTO industrial_hall_data(industrial_hall_token) 
SELECT CONCAT(LEFT(company_name,1) ,LEFT(city,3), RIGHT(post_code,2)) as Result
FROM car_company_data;

And after this my industrial_hall_id column looks like 1,2,3,4... to 21. And that is correct, because I have 21 records in car_company_data. But then I get a problem. When I want to insert one more record, my AI does not work. I have industrial_hall_id column that looks like: 1,2,3,4...,21,32. Do you know any solution for this problem? It is my first post, so sorry for incorrect design, etc,etc. :) Example:
Code: MyCode
Result: MyResult

CodePudding user response：

I have results like 1,2,3,4,5,6,7....,21,32. But I want to have 1,2,3,4,5,6,....,21,22

You can adjust the auto increment value. Execute the following instruction before performing the insertion of new record.

ALTER TABLE Industrial_Hall_Data AUTO_INCREMENT = 22;

CodePudding user response：

Okay, where is the code you use to add one more record. Show the code so I can know where the error is coming from. After each test operation, do you reset autoincrement to its initial value?

For example:

ALTER TABLE table_name AUTO_INCREMENT = 1;

TRUNCATE TABLE table_name;

DROP TABLE table_name; CREATE TABLE table_name { ... };

So that when you try again, the numbering starts from the initial value.