NOT NULL Constraint

What is a NOT NULL Constraint in MySQL?

A NOT NULL Constraint in MySQL is used to prevent a column in a table from storing NULL values, and every record in that column must have a valid value.

Syntax:

CREATE TABLE table_name (
    column_name data_type NOT NULL
);

Example:

Example: Let the Student table contain three columns: Student_ID, Name, and Age, with three rows. The column 'Name' stores the student's name and cannot contain NULL values because it is set with a NOT NULL Constraint.

Output:

+------------+---------+-----+
| Student_ID | Name    | Age |
+------------+---------+-----+
| 101        | Rahul   | 20  |
| 102        | Amit    | 21  |
| 103        | Neha    | 19  |
+------------+---------+-----+

Explanation:

The column 'Name' cannot store NULL values because the NOT NULL Constraint is set on the column.Because a NOT NULL Constraint is set on this column, MySQL does not allow NULL values to be inserted into this field.

How Do You Set a NOT NULL Constraint on a Table in MySQL?

You can set a NOT NULL Constraint on a table by using the following two methods:

1. By using the CREATE TABLE statement while creating a new table.

   
   

   Example:

   CREATE TABLE Student (
       Student_ID INT,
       Name VARCHAR(50) NOT NULL,
       Age INT
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column that you want to make NOT NULL, write the NOT NULL keyword after the data type of that column.
   5. Write the remaining column names with their data types, separating each field with a comma.
   6. Close the parentheses ) and end the query with a semicolon ;.
   8. Finally, you run the query to create the table with the NOT NULL Constraint.

2. By adding a NOT NULL Constraint to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Student
   MODIFY Name VARCHAR(50) NOT NULL;
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the MODIFY clause with the column name and data type.
   4. Write the NOT NULL keyword after the data type of that column.
   5. Close the query and end it with a semicolon ;.
   6. Finally, execute the query to add the NOT NULL Constraint to the existing table.

Advantages of a NOT NULL Constraint

• Prevents NULL Values

  It does not allow NULL values in the column. So, every record must have the required value.

• Maintains Data Accuracy

  If you set a NOT NULL Constraint on a column, it helps keep accurate data by making sure the column always has a valid value.

• Ensures Required Data

  It does not permit NULL values when users insert records into a table. If a user does not enter a value for the column, MySQL shows an error message.

• Improves Data Consistency

  When you define a NOT NULL Constraint on a column, it helps keep the data consistent by preventing missing values in that column when users add records to a table.

Common Mistakes When Setting a NOT NULL Constraint in MySQL

The following are common mistakes that you should avoid:

1. To Insert NULL Values

   You should always enter a value in the column.

2. To Apply NOT NULL on the Wrong Column

   You should choose the correct column for the NOT NULL Constraint. That means you should set it only on a column that must always have a value.

3. To Forget Required Values

   You should always enter a value in every NOT NULL column.

4. To Confuse NOT NULL with UNIQUE Constraint

   You should not confuse a NOT NULL Constraint with a UNIQUE Constraint. A NOT NULL Constraint does not allow NULL values, while a UNIQUE Constraint does not allow duplicate values.

5. Choose the Incorrect Data Type

   You should choose the correct data type for the NOT NULL column.

6. To Apply NOT NULL Without Checking Existing Data

   Before checking and applying a NOT NULL Constraint to a column, you should check the current data.