Create a MySQL table named countries with a NOT NULL constraint on certain columns

MySQL Create Tables: Exercise-5 with Solution

5. Write a MySQL query to create a table countries set a constraint NOT NULL.

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
);

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

Explanation:

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

• COUNTRY_ID: Stores two-letter country codes, and it is marked as NOT NULL to enforce the requirement of having a value in this column.
• COUNTRY_NAME: Stores the names of countries (up to 40 characters), and it is marked as NOT NULL to enforce the requirement of having a value in this column.
• REGION_ID: Stores region IDs with a decimal precision of 10, 0, and it is marked as NOT NULL to enforce the requirement of having a value in this column.

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

Previous: Write a SQL statement to create a duplicate copy of countries table including structure and data by name dup_countries.
Next: Write a SQL statement to create a table named jobs including columns job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000.