how to generate dummy data in mysql using stored procedure in relatable table?

Question

I want to generate dummy data for country and states table and the states table have a relation with the country table using a foreign relation ship here are the tables

MYSQL table

CREATE TABLE country 
 (
 country_name VARCHAR(30) NOT NULL,
 country_phone_code CHAR(12) PRIMARY KEY NOT NULL,
 country_image VARCHAR(255)
);
CREATE TABLE states
 (
 id INT AUTO_INCREMENT PRIMARY KEY,
 state_name VARCHAR(30) NOT NULL,
 country_id INT NOT NULL,
 FOREIGN KEY (country_id) REFERENCES country(country_phone_code)
);

What i was trying was code was to create country list first and then generate for states table and save country id as a foreign key ? this is what so far i got

DELIMITER //
CREATE PROCEDURE GenerateRandomData_05()
BEGIN
 -- Declare variables
 DECLARE i INT DEFAULT 1; -- Loop counter
 DECLARE v_country_phone_code CHAR(12);
 DECLARE v_state_name VARCHAR(30);
 -- Generate random data for the country table
 WHILE i <= 150 DO
 SET v_country_phone_code := SUBSTRING(MD5(RAND()), 1, 12);
 INSERT INTO country (country_name, country_phone_code, country_image)
 VALUES (CONCAT('Country', LPAD(i, 3, '0')), v_country_phone_code, SUBSTRING(MD5(RAND()), 1, 255));
 SET i := i + 1;
 END WHILE;
 -- Reset loop counter
 SET i := 1;
 -- Generate random data for the states, city, and districts tables
 WHILE i <= 500 DO
 -- Generate random state data
 SET v_state_name := CONCAT('State', LPAD(i, 3, '0'));
 INSERT INTO states (state_name, country_id)
 SELECT v_state_name, country_phone_code
 FROM country
 ORDER BY RAND()
 LIMIT 1;
 SET i := i + 1;
 END WHILE;
END //
DELIMITER ;

But a lot of error comes especially with county_phone_code not unknown error ...

Answer 1

Your schema is wrong.

You use FOREIGN KEY (country_id) REFERENCES country(country_phone_code). country_id is INTEGER whereas country_phone_code is CHAR(12). So the creation of the states table will fail.

After fixing this error the code works correctly: fiddle