MariaDB Not Null Constraint
Last Updated :
05 Jan, 2024
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 columsExplanation: 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 values3. 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_ageExplanation: 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.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
Steady State Response In this article, we are going to discuss the steady-state response. We will see what is steady state response in Time domain analysis. We will then discuss some of the standard test signals used in finding the response of a response. We also discuss the first-order response for different signals. We
9 min read