Insert a record into the MySQL 'countries' table to ensure a unique combination of 'country_id' and 'region_id'

MySQL insert into Statement: Exercise-9 with Solution

9. 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.

Create the table countries.

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

INSERT INTO countries VALUES(501,'India',185);

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|        501 | India        |       185 |
+------------+--------------+-----------+

Sample Solution:

-- Inserting a new record into the 'countries' table
INSERT INTO countries VALUES(501, 'Italy', 185);

Let execute the above code in MySQL command prompt.

mysql> INSERT INTO countries VALUES(501,'Italy',185);
ERROR 1062 (23000): Duplicate entry '501-185' for key 'PRIMARY'

Explanation:

Here's a breakdown of the above MySQL query:

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

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

Previous:Write a MySQL query to insert one row in jobs table to ensure that no duplicate value will be entered in the job_id column.
Next: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.