MariaDB Not Null Constraint

MariaDB is an open-source relational database management system. In a relational database, data integrity is very important. So, we use the NOT NULL constraint to ensure the data integrity in a table within a database. So, In this article, we are going to discuss how a NOT NULL constraint helps to maintain data integrity in a table, how to add a NOT NULL constraint to an existing column, and how to remove a NOT NULL constraint with some examples.

NOT NULL Constraint

In MariaDB, the NOT NULL constraint helps to ensure the column values are not NULL. This means that for that column, the value should not be NULL or undefined .ie, a value must be present in the column.

The syntax for applying NOT NULL Constraint in a Column:

Column_name data_type

Consider the table employee(emp_id,emp_name,emp_age,emp_salary).

We are applying a condition(constraint) that emp_id and emp_name should not be a NULL value.

So, we can create the employee table as follows:

CREATE TABLE employee
( 
emp_id INT PRIMARY KEY NOT NULL, 
emp_name VARCHAR(50) NOT NULL, 
emp_age INT, 
emp_salary DECIMAL(8,2) 
); 

If you try not to insert any value to the column emp_id, it will be treated as NULL value and it will show the error:

Adding a NOT NULL Constraint to an Existing Column

Consider the table employee (emp_id,emp_name,emp_age,emp_salary) where emp_name has no NOT NULL constraint. We have to set the table column emp_name to which it should not contain any NULL values but currently it may have NULL values in the column.

1. To Add a NOT NULL Constraint to an Existing Column

• The existing table column that we are planning to apply NOT NULL constraint should not contain NULL values. If it contains NULL values, we should UPDATE the value to a non-NULL value or delete that row.
• Assume that in the table employee, column emp_name contains NULL values. In the above mentioned table, we can set the present NULL values in the column emp_name to ‘None’. For that we can follow the following code:

UPDATE employee 
SET emp_name=’None’ 
WHERE emp_name is NULL; 

Now the values which had NULL values have changed with a value of ‘None’. Now we are ready to apply NOT NULL constraint to that column.

2. MODIFY the Column with NOT NULL Constraint.

To MODIFY the column with NOT NULL constraint, we can modify the table with the following syntax:

ALTER TABLE table_name 
MODIFY  COLUMN column_name data_type NOT NULL; 

In our example we can apply it as:

ALTER TABLE employee 
MODIFY COLUMN emp_name VARCHAR(50) NOT NULL; 

Now we have successfully applied NOT NULL constraint to the column emp_name.

Removing a NOT NULL Constraint

We can modify the table column from which it doesn’t accept the NULL values to it does accept NULL values by using ALTER TABLE statement using the following syntax:

ALTER TABLE table_name 
MODIFY COLUMN column_name data_taype NULL; 

In the above mentioned employee table, we change the emp_name column to which accepts NULL values as follows:

ALTER TABLE employee 
MODIFY COLUMN emp_name VARCHAR(50) NULL; 

Now the column emp_name will acceps NULL values.

Example of NOT NULL Constraint

1. Creation of Table with NOT NULL Constraint

Consider the Employee table which contains emp_id, emp_name, emp_age, emp_salary as columns.

Creating a table with emp_id and emp_name NOT NULL constarint.

CREATE TABLE Employee
( 
emp_id INT PRIMARY KEY NOT NULL, 
emp_name VARCHAR(50) NOT NULL, 
emp_age INT, 
emp_salary DECIMAL(8,2) 
); 

After inserting Some data into the Employee Table our Table Looks Like

Output:

Table with NOT NULL constarint in emp_id and emp_name colums

Explanation: You can see that in the columns emp_id and emp_name doesn’t contain any NULL values because we have applied NOT NULL constraint in both columns. If we try to insert NOT NULL values, it will end up in error.

2. Adding a NOT NULL Constraint to an Existing Column

Consider the table Employee and we haven’t applied NOT NULL constraint to any column so it may contain NULL values.

Table with no NOT NULL constraint in emp_age, so it has NULL values

3. To change the NULL Values to 44

UPDATE Employee 
SET emp_age=44 
WHERE emp_age is NULL; 

Output:

Updated table with no NULL values in emp_age

Explanation: Now the NULL value in emp_age replaced with 44. So, no NULL values in emp_age.

Conclusion

In MariaDB, the NOT NULL constraint helps very much to ensure data integrity in a relational database. So, it helps to maintain the data in a more arranged and efficient manner. It is also very flexible for the user to change the constraint anytime as per the requirements but may become a little complex work. It also acts as a safeguard against the NULL or undefined data in the table. It is also a good practice so the database administrators can optimize it very easily.