Create a MySQL table named countries and enforce a constraint to prevent duplicate values in the country_id column at runtime

MySQL Create Tables: Exercise-11 with Solution

11. Write a MySQL query to create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

Sample Solution:

-- Creating a table named 'countries' if it doesn't already exist to store information about countries

CREATE TABLE IF NOT EXISTS countries(
    -- Column to store the two-letter country code, marked as NOT NULL (required), UNIQUE, and the PRIMARY KEY
    COUNTRY_ID varchar(2) NOT NULL UNIQUE PRIMARY KEY,

    -- Column to store the name of the country (up to 40 characters), marked as NOT NULL (required)
    COUNTRY_NAME varchar(40) NOT NULL,

    -- Column to store the region ID with decimal precision of 10, 0, marked as NOT NULL (required)
    REGION_ID decimal(10,0) NOT NULL
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Explanation:

The provided MySQL code creates a table named "countries" if it doesn't already exist. The table has three columns:

• COUNTRY_ID: Stores two-letter country codes, marked as NOT NULL (required), UNIQUE, and serves as the PRIMARY KEY to ensure each country has a unique identifier.
• COUNTRY_NAME: Stores names of countries (up to 40 characters) and is marked as NOT NULL (required).
• REGION_ID: Stores region IDs with decimal precision of 10, 0, and is marked as NOT NULL (required).

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

Previous: Write a SQL statement to create a table named jobs including columns job_id, job_title, min_salary and max_salary, and make sure that, the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns.
Next: Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto incremented value.