NOT NULL in SQL

Last Updated: 22nd June, 2023

Overview

NOT Null could be a type of data constraint utilized in Structured Query Language (SQL) to guarantee that a column will never contain a Invalid esteem. It prevents clients from entering invalid information into a database table. NOT Null constraints are often utilized in conjunction with other data constraints such as Unique, Primary KEY, and Foreign KEY constraints. The NOT Null constraint can be indicated when a table is made or included later in an existing table.

The SQL NOT NULL Constraint

Not null is a constraint used in SQL that ensures a value is not empty. This is important for businesses and organizations that rely on their databases for day-to-day operations.

Take, for example, a retail store. If a customer purchases an item, the store must record it in its database. The store would use the Not Null constraint to ensure the purchased item is not left blank. With the constraint, the store could inadvertently create a record with a blank item, which would be accurate and lead to unexpected problems.

• This enforces a field NOT to accept NULL values.
• This constraint ensures that when a new row is added to the table, a value will be provided for the column that is specified as NOT NULL.
• If a NULL value is inserted into a NOT NULL column, the database will throw an error and reject the update.
• The NOT NULL constraint is important for data integrity in a database. It helps prevent invalid data from being stored in the column.
• For example, if a "Users" table has a "Username" column, allowing NULL values in that column would not make sense. Every user should have a username. So we would define the "Username" column as NOT NULL.
• Some other common constraints used to ensure data integrity are UNIQUE constraints, PRIMARY KEY constraints, CHECK constraints, and FOREIGN KEY constraints.
• Using these together with NOT NULL constraints guarantees that the data in a database table is accurate, consistent, and reliable. This is an essential property of any good database design

Syntax

CREATE TABLE table_name (
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
);

Example

CREATE TABLE employees (
    id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

The NOT NULL constraint ensures that a column will never contain a NULL value. This constraint is specified when creating a table or can be added later. In the example above, the id, first_name, last_name, and salary columns will not accept NULL values. This means that if a user attempts to enter NULL values in any of these columns, the query will be rejected.

SQL NOT NULL on ALTER TABLE

The following statement can be used to set a column to NOT NULL on an ALTER TABLE statement:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NOT NULL;

Example

Table Name: Customers

ALTER TABLE Customers
MODIFY COLUMN Name VARCHAR(50) NOT NULL;

This code alters the Customers table by modifying the Name column. The data type of the Name column is set to VARCHAR(50), and the NOT NULL constraint is added, which means that the Name column must have a value in it.

Conclusion

In conclusion, Not Null is an important constraint used in SQL to ensure accurate data is recorded in databases. The retail store must use the Not Null constraint in its database to record customer purchases accurately. Without the constraint, the store could inadvertently create a record with a blank item, which would lead to unexpected problems.

Key takeaways

1. NOT Null may be a constraint in SQL that is utilized to guarantee that a column in a table cannot contain Invalid values.
2. NOT Null can be indicated when making a table or changing an existing one.
3. NOT Null can be utilized with other constraints, such as Unique or Primary KEY, to implement data integrity.
4. It is important to note that NOT Null isn't a data type and can be utilized with any data type.
5. To set a column to NOT Null in SQL, utilize the syntax Modify TABLE table_name Alter COLUMN column_name SET NOT Null.

Quiz

1. What is the correct syntax for a NOT NULL constraint in SQL? 

1. NOT NULL 
2. NOT EQUAL NULL 
3. <> NULL 
4.  IS NOT NULL

Answer: d. IS NOT NULL

2. What type of constraint is NOT NULL? 

1. Unique 
2. Primary Key 
3. Check 
4. Foreign Key

Answer: c. Check

3.Can a NOT NULL constraint be added to an existing column? 

1. Yes 
2. No

Answer: a. Yes

4. What is the result of attempting to insert a NULL value into a column with a NOT NULL constraint?

1. The value is accepted 
2. The value is rejected

Answer: b. The value is rejected