What is Not Null Constraint in SQL?

If you are working with SQL databases, then you are familiar with the concept of constraints. In SQL, the not null constraint is used to ensure that a column in a table can not contain NULL values. A NULL value is a special indicator used in SQL that means a data value does not exist in the column of a table.

Introduction to Not Null Constraint in SQL

A not null constraint is a database constraint used to ensure that a column in a table does not contain empty values or a null value. It is an essential part of database management systems as it helps to maintain data integrity and consistency. In this article, you will look at what is a not-null, how it works, and why it is important in the database management system.

What is a Not Null Constraint in SQL?

A not null constraint is a database constraint that ensures a particular column in a table must always contain a specific value. When a not-null constraint is applied to a column, That means the column cannot be blank or contain a null value. SQL Server allows a null value by default for all columns if not null is not implemented. This constraint is used to ensure data integrity and prevent inconsistencies in the database.

How does a Not Null Constraint work in SQL?

A not null constraint can be applied to a column during the creation of a table. When a column has a Not Null Constraint, that means a value must be entered into the column for every row inserted or updated into the table. If a null value is attempted to be inserted or updated into the column, SQL Server DBMS will throw an error, and the insertion or updation will fail.

For example, let’s assume that we have a table called “NN_Employees” with columns for “EmployeeID“, “FirstName“, “LastName“, and “Email“. If we apply a not null to the “Email” column, that means every row in the table must have a value in the “Email” column. If we attempt to insert or update a row without a value for the “Email” column, an error will occur, and the insertion or updation will fail.

Importance of Not Null Constraint in SQL

The not-null is a very essential feature for maintaining data integrity and consistency in the database. Without it, there is a risk of inconsistent and inaccurate data in the database, which can lead to errors and incorrect results.

Not-null can also help with data analysis and reporting. When data is consistent and accurate, it is easier to analyze and generate reports. The not-null ensures that the data is valid, and the reports generated are reliable.

Advantages of Not Null Constraint in SQL

Refer to below the advantages of not-null in SQL –

1. Not-null can ensure that all data entered into a database is valid and accurate. This helps to maintain the integrity of the data and prevent errors.
2. By enforcing the not-null, we can ensure that the data in a table is consistent.
3. Not-null can simplify data entry by ensuring that all required fields are filled. This can help to save time and improve efficiency.
4. It prevents the insertion or updation of incomplete or invalid data into the database. This helps maintain the overall quality of the database.

Disadvantages of Not Null Constraint in SQL

Refer to below the disadvantages of not-null in SQL –

1. One of the potential disadvantages of a not-null is that it can make data entry more difficult. For example, if a user is trying to add a record to a table but does not have a value for the “email” column, they may be prevented from doing so, even if the value is not strictly necessary.
2. Another potential disadvantage of a not-null is that it can limit the flexibility of a database. For example, if we have a column that we want to allow null values for in some cases, we may not be able to do so if the Not Null Constraint is applied.

Best Practices for Using Not-Null

Refer to below the best practices for using the not-null constraints in SQL –

1. While the Not Null Constraint can be more useful, it is important to use it fairly.
2. You should only apply the constraint to columns that require a value for every record in the table.
3. In some cases, you may be able to use default values to avoid the potential disadvantages of using Not Null Constraint. If you have a column in that you want to allow null values, you can define a default value that will be used if no value is provided while insertion or updation data.

Applications of Not-Null in SQL

You will learn here the application of not null constraint in SQL as follows –

1. Not-Null Creation Without Using Default While Table Creation

In this example, we will create a table named ‘NN_Employees1‘ and assign a not-null constraint with the ‘DateOfJoining’ and ‘Age’ columns, where we are not assigning default constraint. In this scenario, DBMS will throw an error if the user does not provide any value for these columns while inserting any records into the table, which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_Name4 Data_Type,
Column_Name5 Data_Type NOT NULL,
Column_Name6 Data_Type NOT NULL);

Example:

CREATE TABLE NN_Employees1 (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50),
DateOfJoining DATE NOT NULL,
Age INT NOT NULL);

Screenshot: Below screenshot for reference –

2. Not-Null Creation Using Default While Table Creation

In this example, we will create a table named ‘NN_Employees2’ and assign a not-null constraint with the ‘DateOfJoining’ and ‘Age’ columns, where the default value is the ‘current date’ for ‘DateOfJoining’ column, and the default value is ’18’ Years for ‘Age’ column. In this scenario, DBMS will insert the ‘current date’ in ‘DateOfJoining’ and ’18’ in the ‘Age’ columns if the user does not provide any value for these columns while inserting any records into the table, which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_Name4 Data_Type,
Column_Name5 Data_Type NOT NULL DEFAULT Default_Value,
Column_Name6 Data_Type NOT NULL DEFAULT Default_Value);

Example:

CREATE TABLE NN_Employees2 (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50),
DateOfJoining DATE NOT NULL DEFAULT GETDATE(),
Age INT NOT NULL DEFAULT '18');

Screenshot: Below screenshot for reference –

You can see in this scenario, not-null constraints have been created with default constraints successfully for both columns, and also both default constraints created (DF__NN_Employ__DateO__0B5CAFEA and DF__NN_Employee__Age__0C50D423), which can be seen in the above screenshot marked with yellow color on the left side.

3. Not-Null Creation Without Using Default After Table Creation

In this example, We will create not-null constraints without using default constraints with the ‘DateOfJoining’ and ‘Age’ columns of ‘NN_Employees3’ table, where we are not assigning default constraints. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ALTER COLUMN Column_Name Data_Type NOT NULL;

Example:

ALTER TABLE NN_Employees3
ALTER COLUMN DateOfJoining DATE NOT NULL;

ALTER TABLE NN_Employees3
ALTER COLUMN Age INT NOT NULL;

Screenshot: Below screenshot for reference –

4. Not-Null Creation Using Default After Table Creation

In this example, we will create a not-null constraint and set default values with the ‘DateOfJoining’ and ‘Age’ columns with an existing table named ‘NN_Employees4’. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ALTER COLUMN Column_Name Data_Type NOT NULL;

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name DEFAULT Default_Value FOR Column_Name;

Example:

ALTER TABLE NN_Employees4
ALTER COLUMN DateOfJoining DATE NOT NULL;

ALTER TABLE NN_Employees4
ADD CONSTRAINT DF_NN_Employees4_DOJ DEFAULT GETDATE() FOR DateOfJoining;

ALTER TABLE NN_Employees4
ALTER COLUMN Age INT NOT NULL;

ALTER TABLE NN_Employees4
ADD CONSTRAINT DF_NN_Employees4_Age DEFAULT '18' FOR Age;

Screenshot: Below screenshot for reference –

You can see in this scenario, not-null constraints have been created with default constraints successfully for both columns, and also both default constraints created (DF_NN_Employees4_Age and DF_NN_Employees4_DOJ), which can be seen in the above screenshot marked with yellow color on the left side.

5. Disable or Delete Not-Null and Allow Null Value on a Column

In this example, You will learn here that how to disable or delete a not-null constraint and allow null values with the ‘DateOfJoining’ and ‘Age’ columns with an existing table named ‘NN_Employees4’. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ALTER COLUMN Column_Name Data_Type NULL;

Example:

ALTER TABLE NN_Employees4
ALTER COLUMN DateOfJoining DATE NULL;

ALTER TABLE NN_Employees4
ALTER COLUMN Age INT NULL;

Screenshot: Below screenshot for reference –

You can see in this scenario, not-null constraints have been removed successfully for both columns and allowed null values, which can be seen in the above screenshot marked with yellow color on the left side.

Test Scenarios of Not-Null in SQL Server

You will learn here all possible test scenarios of not-null constraints that are working or not using INSERT – SELECT statements. If you remembered that we have created the ‘NN_Employees3’ table and implemented a not-null constraint on the ‘DateOfJoining’ and ‘Age’ columns, which does not allow null values for both columns. We will take this table for testing purposes with an insert statement. We will try to insert a record but we will not provide the values for above both columns let’s see. Ideally, not-null constraints should throw an error and roll back the query operation. Refer to the below example –

Example:

INSERT INTO NN_Employees3 (EmployeeID, FirstName, LastName)
VALUES ('12','SQLPOST','Academy');

Screenshot: Below screenshot for reference –

You can see in the above screenshot that we have not provided values for the ‘DateOfJoining’ and ‘Age’ columns and hence the SQL Server DBMS terminated the statement and threw an error ‘Cannot insert the value NULL into column ‘Age’, table ‘SQLPOST.dbo.NN_Employees3′; column does not allow nulls. INSERT fails. The statement has been terminated.’, Now we will try the same statement but by providing values for both columns, and let’s see –

Example:

INSERT INTO NN_Employees3 (EmployeeID, FirstName, LastName,DateOfJoining,Age)
VALUES ('12','SQLPOST','Academy','2020-01-01','18');

SELECT * FROM NN_Employees3;

Screenshot: Below screenshot for reference –

You can see in the above screenshot that we have provided the values for both columns ‘DateOfJoining’ and ‘Age’ and the record is inserted successfully, which means the not-null constraints are working fine.

Note: We have tried to explain all possible scenarios about the not-null constraint at a summary level, where you have to read the complete article and try to elaborate on all possible scenarios with you and give more time to practice it.

FAQs

Conclusion

The Not Null Constraint is a powerful feature of database management systems that helps to maintain data accuracy, consistency, and integrity. It ensures that data is consistent and accurate, and also prevents inconsistencies and errors. Applying a Not Null Constraint to a column is a simple and effective way to maintain data integrity in the database. By understanding the importance of Not Null Constraint, you can ensure that your database is reliable and accurate. We hope you would have liked this very well.

Please share this on your social media network so that it can be available to even more needy persons, which may benefit them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What are Integrity Constraints in DBMS?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL
Unique Key in DBMS and Best Practices	Foreign Key in DBMS and Best Practices
What is Default Constraint in SQL?	What is Check Constraint in SQL?