Insert rows into the MySQL 'countries' table with auto-incremented unique values for the 'country_id' column

MySQL insert into Statement: Exercise-10 with Solution

10. Write a MySQL query to insert rows into the table countries in which the value of country_id column will be unique and auto incremented.

Create the table countries.


CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID integer NOT NULL
);

Sample Solution:

-- Inserting a new record into the 'countries' table with specified columns
INSERT INTO countries(COUNTRY_NAME, REGION_ID) VALUES('India', 185);

Let execute the above code in MySQL command prompt.

Here is the structure of the table:

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|          1 | India        |       185 |
+------------+--------------+-----------+
1 row in set (0.00 sec)

Explanation:

Here's a breakdown of the above MySQL query:

• INSERT INTO countries: Specifies the insertion operation into the 'countries' table.
• (COUNTRY_NAME, REGION_ID) VALUES('India', 185): Specifies the values to be inserted into the specified columns of the 'countries' table. In this case:
• 'India' is inserted into the 'COUNTRY_NAME' column.
• 185 is inserted into the 'REGION_ID' column.

INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('Japan',102);

Let execute the above code in MySQL 5.6 command prompt.

Here is the structure of the table:

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|          1 | India        |       185 |
|          2 | Japan        |       102 |
+------------+--------------+-----------+
2 rows in set (0.03 sec)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to insert a record into the table countries to ensure that, a country_id and region_id combination will be entered once in the table.
Next:Write a MySQL query to insert records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned for that column.