Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is SQL Not Null?
3.
Syntax 
4.
Use Case examples
4.1.
Creating a new table with a "NOT NULL" constraint
4.2.
Adding a NOT NULL constraint to an existing column
4.3.
Removing NOT NULL constraint from a column
5.
SQL Not Null with SQL Unique Constraint
6.
Real-World Examples of SQL Not Null Implementation
7.
Frequently Asked Questions
7.1.
Can you remove the NOT NULL constraint from a column in SQL?
7.2.
Can you use the NOT NULL constraint with all data types in SQL?
7.3.
Is it best practice to use the "NOT NULL" constraint on all columns in a table?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

SQL NOT NULL

Introduction

Suppose you have made an application where you accept some input from the user. Now, what if you want some fields to be mandatory added? For that, you must add a constraint to make the field necessary to add. 

Not to worry! 

SQL Not NULL is a constraint to ensure that the data must be inserted into the column that is not null

 

SQL NOT NULL

 

SQL Not NULL is a great way to ensure data integrity and a simple yet effective way to make sure that the data must get added. 

In this article, we will discuss the SQL NOT NULL constraint along with several examples. 

Keep reading to learn more about Sql Not null!

Also Read, LIMIT in SQL

What is SQL Not Null?

By default, in SQL, each column can support Null values. But in many cases, a user may want to have something other than NULL or missing values in their table or database. That’s when the Not Null command of SQL comes into play. This property is essential when we want that each column must contain a value and should not be left empty.

Let us now see the syntax of SQL NOT NULL: 

Syntax 

The following syntax adds the NOT NULL constraint to the column: 

CREATE TABLE Table_Name  
(  
Column_Name_1 DataType (character_size) NOT NULL,  
Column_Name_2 DataType (character_size) NOT NULL,  
...
...  
Column_Name_N DataType (character_size) NOT NULL,  
)  ; 


Let us now see some examples of SQL NOT NULL constraint: 

Use Case examples

Here, we will see many use cases of SQL NOT NULL constraint: 

Creating a new table with a "NOT NULL" constraint

Let us create a employees table and add some properties to it: 

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);


The schema for the table with some entries looks like this:

CREATE WITH NOT NULL

This creates a table called "employees" with three columns: "id," "name," and "age". All three columns have the "NOT NULL" constraint, meaning that values must be entered for each column when a new row is added to the table.

If you try to insert a NULL value into the table like you write the query:

INSERT INTO employees(id,name,age)
 VALUES(3,NULL,20)

Then you get the following error:

Error: NOT NULL constraint failed: employees.name

Adding a NOT NULL constraint to an existing column


Let us see a different use case where we alter or modify the existing column: 

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  age INT 
);


So now there isn't a NOT NULL condition enforced beforehand. So we can add NULL entries to the table like the one given below and have no error :

INSERT INTO employees(id,name,age)
VALUES(4,NULL,24)

After this query executes, the table looks like this:

ALTER TO NOT NULL

We can enforce the NOT NULL condition on the age column so that when we add a new entry, the user cannot leave the age empty; we do this using the alter command:

ALTER TABLE employees
ALTER COLUMN age INT NOT NULL;

Now after this, when we try to add an entry like this:

INSERT INTO employees(id,name,age)
VALUES(4,'Zane',NULL)

Then we get the following error:

Error:NOT NULL constraint failed: employees.age

This alters the "employees" table and adds the "NOT NULL" constraint to the "age" column. Any rows with a NULL value in the "age" column must be updated before applying the constraint.

Removing NOT NULL constraint from a column

Now what if the user wants to remove the NOT NULL constraint from the existing column. We can also do this by using following syntax: 

ALTER TABLE employees
ALTER COLUMN age INT NULL;

After writing such a Query, we can now add NULL values to the age column, so when writing the below query, there will be no error:

INSERT INTO employees(id,name,age)
VALUES(4,'Zane',NULL)

 

REMOVE NOT NULL



This alters the "employees" table and removes the "NOT NULL" constraint from the "age" column. This allows NULL values to be entered in the "age" column for any new rows added to the table.

Best Practices for Using SQL Not Null
  
When using the SQL Not Null constraint, it is important to follow best practices to ensure it is used correctly and effectively. Here are some best practices to consider:

●  Use the SQL Not Null constraint for all required fields in the database

●  Ensure that the constraint is applied consistently across all tables in the database
 
●  Avoid using the constraint for optional fields or fields that may be null in some cases
 
●  Ensure that the constraint does not conflict with other conditions or validation rules 
 
●  Be aware of the impact of the constraint on data entry and data quality
  
By following these best practices, you can ensure that the SQL Not Null constraint is being used effectively and contributes to the overall integrity of the database.

SQL Not Null with SQL Unique Constraint

While the SQL Not Null constraint effectively enforces data integrity, other constraints and validation rules can be used in conjunction with the SQL Not Null constraint to make the best use of SQL NOT NULL constraint. 

For example, the SQL Unique constraint can ensure a column contains unique values. These constraints can be combined with the SQL Not Null constraint to provide a more comprehensive approach to data integrity.

When you use SQL commands like unique along with NOT NULL commands, you can further maintain the data pushed into your table. 

Let’s have a look at an example:

CREATE TABLE employees (
id int NOT NULL UNIQUE,
name varchar(255) NOT NULL,
Age int
);

And we insert an entry using the command:

INSERT INTO employees
VALUES(1,'sid',23)

Then we get the table:
 

NOT NULL WITH UNIQUE


But now, if we type the Query :

INSERT INTO employees
VALUES(1,’zane’,23)

 Now we would get an error because we set the id property to be unique and not null, but an entry already exists with id=1, so we get the following error, also when we try to put a record with id as Null then also we will get error:

Error: UNIQUE constraint failed: employees.id

 

Must Read SQL Clauses

Real-World Examples of SQL Not Null Implementation

Here are some real-world examples of how SQL Not Null constraint, is used in database management:
●  A healthcare organization uses the SQL Not Null constraint to ensure that patient records contain all required information, such as name, date of birth, and medical history.

●  A financial institution uses the SQL Not Null constraint to ensure that all financial transactions contain all required data, such as account numbers, transaction amounts, and transaction dates.

●  An e-commerce company uses the SQL Not Null constraint to ensure that all customer orders contain all required data, such as order numbers, product information, and shipping addresses.
  
In each of these examples, the SQL Not Null constraint is used to ensure that the data in the database is accurate, reliable, and consistent. 

Also see, Tcl Commands in SQL

Frequently Asked Questions

Can you remove the NOT NULL constraint from a column in SQL?

You can remove the NOT NULL constraint from a column in SQL by altering the table and removing the "NOT NULL" keyword from the column definition. However, this may result in incomplete or inconsistent data being introduced into the table.

Can you use the NOT NULL constraint with all data types in SQL?

Yes, the NOT NULL constraint can be used with all data types in SQL. In SQL, you can use NOT NULL with data types, including strings, numbers, and dates.

Is it best practice to use the "NOT NULL" constraint on all columns in a table?

It depends on the data and the use case. In some cases, allowing NULL values in a column may be appropriate, but in others, enforcing the "NOT NULL" constraint may be important to ensure data integrity.

Conclusion

The SQL Not Null constraint is a critical component of database integrity. It helps ensure that the data in a database is accurate, reliable, and consistent by preventing null values from entering the table. By following best practices and implementing the constraint correctly, you can maintain high data integrity and avoid common issues caused by null values. Want to learn more about topics like this? Don't worry Coding Ninjas have you covered. Refer to :NULL Values,Constraints in SQL Server,Constraints.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enrol in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Happy Learning!
 

Live masterclass