w3resource

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

MySQL Create Tables: Exercise-9 with Solution

9. Write a MySQL query to create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed 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)
    COUNTRY_ID varchar(2) NOT NULL,

    -- 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,

    -- Adding a UNIQUE constraint on the COUNTRY_ID column to enforce uniqueness
    UNIQUE(COUNTRY_ID)
);

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 and is marked as NOT NULL (required).
  • COUNTRY_NAME: Stores country names (up to 40 characters) and is marked as NOT NULL (required).
  • REGION_ID: Stores region IDs with a decimal precision of 10, 0 and is marked as NOT NULL (required).

Additionally, a UNIQUE constraint is applied to the COUNTRY_ID column, ensuring that each country has a unique two-letter code within the table.

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

Previous: Write a SQL statement to create a table named job_histry including columns employee_id, start_date, end_date, job_id and department_id and make sure that the value against column end_date will be entered at the time of insertion to the format like '--/--/----'.
Next: 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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.