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:
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.
What is the difficulty level of this exercise?