NOT NULL

NULL values are refered to the space in a column where no value exist. It should not be confused with '0'. '0' or zero is a value. However, NULL value in a column means that that element of the column does not contain any value. In our example table students, we want each students in the table to have student_id, first_name and last_name. SImilarly, we do not want null values in the columns game_id and game_name of the games table. So we set NOT NULL to those columns. 

This constraint thus prevents table to have NULL values. Whenever, we provide NULL values to these columns, we will get an error. Lets take a look at it. 


Use following statements to insert all the example data into the table students.

INSERT INTO students VALUES (1, 'Saurav', 'Shrestha', 'sshrestha@xyz.com');

INSERT INTO students VALUES (2, 'John', 'Smith', 'jsmith@abc.com');

INSERT INTO students VALUES (3, 'Jason', 'Brock', 'jbrock@abc.com');

INSERT INTO students VALUES (4, 'Jenny', 'Smith', 'jsmith@xyz.com');

INSERT INTO students VALUES (5, 'John', 'Kelly', 'jkelly@abc.com');

Using SELECT statement, if we look at the students table now in MySQL, we can see a fully developed table as in an example. 

Table 1. Students Table

Using DESCRIBE statement, we can see the details of the table created. In the description below (Table 2), Null column is added which is the constraint we provided to the column_fields of the table. So, it means that student_id, first_name and last_name columns cannot have null values. However, email can be null.

DESCRIBE students;

Table 2. Describe students Table

Let's try inserting a null value in first_name of student_id = 2...

MySQL will give you an error saying the column cannot be NULL since we provided the NOT NULL constraint to the column.

NOT NULL and ALTER TABLE

We created a table "students" and provided NOT NULL constraints to the columns.  If you remember, we, however, have no constraints on the column "email". That means the column email can have NULL values and we know that very well. But, what if we want that column to have the NOT NULL constraint too later in our project, when the table is already created?

Using ALTER Table, we can easily do that.  

ALTER TABLE students 

MODIFY email VARCHAR(255) NOT NULL;

We can use the similar statements to create, alter and provide constraints to the table 'games' as well.  Now let's move on to the next constraint UNIQUE.