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.

Go to:

• MySQL Create Table Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : 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.

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