Create a MySQL table 'countries' with a unique and auto-incremented 'country_id' column

MySQL Create Tables: Exercise-12 with Solution

12. Write a MySQL query 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.

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 an integer-based country identifier, marked as NOT NULL (required), UNIQUE, AUTO_INCREMENT, and serving as the PRIMARY KEY
    COUNTRY_ID integer NOT NULL UNIQUE AUTO_INCREMENT 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
);

-- Displaying the structure of the 'countries' table using the DESC command
DESC countries;

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   | int           | NO   | PRI | NULL    | auto_increment |
| COUNTRY_NAME | varchar(40)   | NO   |     | NULL    |                |
| REGION_ID    | decimal(10,0) | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Explanation:

The above MySQL code -

• Create a table named 'countries' if it doesn't already exist, with three columns:
• COUNTRY_ID: An integer-based country identifier marked as NOT NULL, UNIQUE, and AUTO_INCREMENT, serving as the PRIMARY KEY.
• COUNTRY_NAME: A column to store the name of the country (up to 40 characters), marked as NOT NULL.
• REGION_ID: A column to store the region ID with decimal precision of 10, 0, marked as NOT NULL.
• Use the DESC command to display the structure of the 'countries' table, showing information about its columns.

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

Previous: Write a SQL statement 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.
Next: Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.