SQL NOT NULL Constraint

The SQL NOT NULL constraint is used to restrict a column to NOT accept NULL values.

By default, a field can store NULL values. If you do not want a field to have a NULL value, then you need to define that column as NOT NULL constraint.

The SQL NOT NULL constraint enforces a column to always contain a value except NULL value. This means that you cannot insert a new row, or update a existing row without adding or setting a value to this column.

The ALTER command will be used to add a not null constraint to an existing column in MySQL. This sort of validation prevents users from typing in null values.

The steps for adding a not null constraint to an existing column are as follows:

• If possible, replace null values with non-null ones.
• Second, provide a not null constraint in the column.

If you try to apply a NOT NULL constraint to a column in PostgreSQL, it will be done as an atomic operation, such as ALTER TABLE table name ALTER_COLUMN column name SET NOT_NULL.

Example 1: For example, to add a not null constraint to the summary column of the courses table, you use these steps:

First, update null values in the summary column to non-null values:

update courses
set summary = 'N/A'
where summary is null;

Second, modify the summary column to include a not null constraint:

alter table courses
modify summary varchar(255) not null;

Example 2: Let us see an example.

Step 1: Firstly, we will create a table. The CREATE command is used to create a table.

create table AddNotNUlldemo
   - > (
   - > name varchar(100)
   - > );
Query OK, 0 rows affected (0.44 sec)

Step 2: To insert records.

insert into AddNotNUlldemo values('John');
Query OK, 1 row affected (0.19 sec)

insert into AddNotNUlldemo values('Bob');
Query OK, 1 row affected (0.19 sec)

Step 3: To display all the records.

select *from AddNotNUlldemo;

Output:

Step 4: The following is the syntax to add a constraint to an existing column.

ALTER table yourTableName  modify column_name data type constraint;

Let us now implement the above syntax to implement the below query. Here, we are including “not null” constraint.

ALTER table AddNotNUlldemo modify name varchar(100) not null;

To change a column in SQL Server from allowing null values to not accepting null values. Upgrading a column from NULL to NOT NULL requires using the ALTER TABLE syntax to update the table.

To change a column from null to non null, you must take a few actions.

Verify the column for any NULL values, and if you discover one or more, replace them with default values (on INT columns it is common to use 0)

After the modification has been made, use ALTER TABLE to make the change to the column. TableName CHANGE THE COLUMN NOT NULL ColumnName Datatype; Verify that the update worked as planned.

Changing the data structure of a column in SQL Server from NULL to NOT NULL, hence preventing non-null values in that column, is usually accomplished with the ALTER TABLE command.

You have to take two steps:

1. Update the table to delete all NULL values:

UPDATE table_name 
SET col_name = 0
WHERE col_name IS NULL;

2. Alter the table and change the column to not nullable:

ALTER TABLE table_name
ALTER COLUMN col_name data_type NOT NULL;

If you also want to add a default value to that column, run this query after two steps above:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name DEFAULT default_value FOR col_name;

Example 1: So, we will create a database first:

Step 1: Creating Database

CREATE DATABASE GFG

Step 2: Using database

USE GFG

Step 3: Create a table with a null column

CREATE TABLE gfgTutorial(
id integer,
Name varchar(20)
)

Describe the table
sp_help 'dbo.gfgTutorial'

Step 4: Change the id column to not null

ALTER TABLE gfgTutorial ALTER COLUMN id VARCHAR (50) NOT NULL;

Example 2:

On this example we are going a create a table and call it users, then fill it with some demo data.

--Creating demo table
Create Table users(
 username varchar(250)
 ,age int NULL
)

--Inserting demo data into the table
INSERT INTO users
VALUES   ('User1',NULL)
 ,('User2',25)
 ,('User3',22)
 ,('User4',NULL)
 ,('User5',32)

The generated table has a field called 'age' that accepts NULL values, as you can see. In this column, we also included some NULL values.

3. How To Check If Columns Does Contain NULL Values

The first step is to ensure that the change target column does not have any NULL values, which you can do by using the ISNULL function in the WHERE clause.

Example :

SELECT * 
FROM users 
WHERE age IS NULL

Then we must replace any NULL entries in the column with default values (something that you pick, It depends on what kind of data do you have)

Example :

UPDATE users
SET age = 0
WHERE age IS NULL

We kept the format of the SELECT statement we used to identify the NULL values table and transformed it to a UPDATE statement in this update.

As you can see, all NULL-containing records were changed.

The default value for updating the table is determined by the datatype of the column and your conditions.

4. How To Change A Column From NULL To NOT NULL Using ALTER TABLE

After we've changed the NULL values on the table, we'll move on to modifying the table structure.

Example :

ALTER TABLE users
ALTER COLUMN age VARCHAR(250) NOT NULL;

In this page, we have discussed how to test if a date value is not NULL.

Example:

Sample table: newpublisher

Code:

SELECT pub_name,pub_city,country,estd 
FROM newpublisher         
WHERE estd IS NOT NULL;

Explanation

The above MySQL statement will filter the rows whose estd date is NOT NULL.

Output:

When you use the COUNT() function with a column name, only non-NULL values in that column are counted. The total number of rows in the result set is calculated using the COUNT() method. Use the aggregate function COUNT to count the number of times a NOT NULL value appears (yourColumnName).

COUNT(*) gives all the rows in the table, whereas COUNT(Expression) skips Null expressions. Count() will return all rows with a non-null value if you specify a column name that supports NULL values.

Example 1:

Let us first create a table −

create table DemoTable
 (
 Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 NumberOfQuestion int,
 NumberOfSolution int
 );
Query OK, 0 rows affected (0.20 sec)

Insert some records in the table using insert command. Here, some of the values are NULL:

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(20,10);
Query OK, 1 row affected (0.06 sec)

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(20,2);
Query OK, 1 row affected (0.04 sec)

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(20,NULL);
Query OK, 1 row affected (0.03 sec)

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(20,NULL);
Query OK, 1 row affected (0.05 sec)

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(30,19);
Query OK, 1 row affected (0.04 sec)

insert into DemoTable(NumberOfQuestion,NumberOfSolution) values(30,1);
Query OK, 1 row affected (0.04 sec)

Display all records from the table using select statement:

select *from DemoTable;

This will produce the following output

Following is the query to count presence of value. The same value should be NOT NULL i.e. NULL values won’t be counted:

SELECT NumberOfQuestion, COUNT(NumberOfSolution) as NumberOfRows FROM DemoTable GROUP BY NumberOfQuestion;

Output:

Example 2: when we count the Person table through the following query, it will return 19972.

SELECT COUNT(*) AS [Total Number of Rows] FROM Person.Person

How to count SQL NULL values in a table

When we use the COUNT() function with a column name, however, only non-NULL values in that column are counted.

SELECT COUNT(Title) AS [Total Number of Title] FROM Person.Person

In order to count NULL values of a column, we can use the following query.

SELECT SUM(CASE WHEN Title is null THEN 1 ELSE 0 END) 
AS [Number Of Null Values] 
, COUNT(Title) AS [Number Of Non-Null Values] 
FROM Person.Person

In MYSQL, the storage space is allocated separately to the fractional and integer components of a decimal number, that is, the value before and after the decimal point. Because MYSQL uses the binary format to store values for the decimal data type, it takes 4 bytes of memory to store 9 digits. Each pack of 9 digits before and after the decimal point requires 4 bytes of storage space. The remaining digits require storage space in the following manner:

1 or 2 residual digits take up 1 byte, 3 or 4 leftover digits take up 2 bytes, 5 or 6 leftover digits take up 3 bytes, and 7 or 9 leftover digits take up 4 bytes.

Considering the DECIMAL(20,8) datatype, which will require 4 bytes of storage space for the 8 digits following the decimal point and 20-8=12, i.e. the remaining 12 digits will be split in half. Nine digits will take four bytes, while the remaining 12-9, or three digits, will require two bytes. As a result, storing the decimal value of this type will take up a total of 4+4+2 =10 bytes.

Example: Let us create one table that will contain the decimal data typed column in it. Here, I will create a table named educba_stock containing column cost_in_rupees of decimal datatype with precision 20 and scale 2 using following create query:

CREATE TABLE educba_stock (
identifier INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(100),
cost_in_rupees DECIMAL(20 , 2 ) NOT NULL
);

that results in the following output :

Let us insert some records in it by executing the following command

INSERT INTO educba_stock(description,cost_in_rupees)
VALUES('Java', 4500.26),('Angular',8512.6),('Mysql',4587.45);

that gives the following output in sqlyog editor :

Let us now retrieve the data stored in educba_stock table using the following select query :

SELECT * FROM educba_stock;

There is no need to specify the values because they are null by default.

Example : Let us create one table that will contain the decimal data typed column in it. Here, I will create a table named educba_stock containing column cost_in_rupees of decimal datatype with precision 20 and scale 2 using following create query:

CREATE TABLE educba_stock (
identifier INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(100),
cost_in_rupees DECIMAL(20 , 2 ) NULL
);

Let us insert some records in it by executing the following command

INSERT INTO educba_stock(description,cost_in_rupees)
VALUES('Java', 4500.26),('Angular',8512.6),('Mysql',);

that gives the following output in sqlyog editor :

Let us now retrieve the data stored in educba_stock table using the following select query :

SELECT * FROM educba_stock;

SQL COALESCE is a function that returns the first non-NULL value from a list of arguments. The column of the table to which the query is addressed is usually one or more COALESCE function arguments. A subquery is frequently used as a function argument.

Syntax:

COALESCE(val1, val2, val3 …, valn)

For example:

SELECT COALESCE(NULL, NULL, 5, NULL, 10) as NUM -- return 5

Because this is the first Non-Null value in the list, this query returns 5.

Example 1: We can simply supply the column name as the first parameter to the COALESCE function and the value to substitute as the second argument to replace all the Null values in our christmas spending column.

SELECT id,
 name,
 gender,
 COALESCE(christmas_spending, 0) as christmas_spending
FROM
 actors_info

Example 2:

Step 1: Let us first create a table :

create table DemoTable1927
   (
   StudentName varchar(20),
   StudentSubject varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Step 2: Insert some records in the table using insert command :

insert into DemoTable1927 values('Chris','MySQL');
Query OK, 1 row affected (0.00 sec)

insert into DemoTable1927 values('David',NULL);
Query OK, 1 row affected (0.00 sec)

insert into DemoTable1927 values(NULL,'MongoDB');
Query OK, 1 row affected (0.00 sec)

Step 3: Display all records from the table using select statement :

select * from DemoTable1927;

Output :

Step 4: Here is the query to implement coalesce() in MySQL :

select coalesce(StudentName,StudentSubject) as Result from DemoTable1927;

Output :

Example 3: There is a library database and a BOOKINUSE (Book in Issue) table. The table looks like this:

Because the issued edition is a journal, the last line does not have a specific value for the Author field. Allow authors of published editions with specific inventory numbers to be listed, and no fields should be left blank. We build a query utilising the COALESCE function for this purpose:

SELECT COALESCE (Author, 'Magazine')
AS InUse
FROM Bookinuse
WHERE inv_no IN (25, 81, 127)

SQL NULL

A blank value in a table is represented by a SQL NULL value. The NULL value is used to detect any missing table items.

The NOT NULL value can be used to display table entries that are not NULL.

Syntax of NULL Value :

SELECT column_name(s)
FROM table_name
Where column_name IS NULL;
SELECT column_name(s)
FROM table_name
Where column_name IS NOT NULL;

SQL NULL STATEMENT Example:

Database table “Employee”

Note: In the "Employee" table above, the "Location" field is optional. As a result, if no value is entered for the "Location" column, the "Location" field will be stored with a NULL value.

Here is an instance of a statement that can be used to display entries whose location has been left blank.

SELECT * FROM Employee
WHERE Location IS NULL;

SQL NULL Statement Output:

SQL NOT NULL Statement

Now one wants to display the field entries whose location is not left blank, then here is a statement example.

SELECT * FROM Employee
WHERE Location IS NOT NULL;

SQL NOT NULL Statement Output:

In SQL, the IS NOT NULL condition is used to check for a value that is not NULL. If a non-NULL value is detected, TRUE is returned; otherwise, FALSE is returned. It's suitable for SELECT, INSERT, UPDATE, and DELETE statements.

You might use the following instead of IS NOT NULL in the script:

WHERE NOT and => are used to select only non-null values. When WHERE is followed by NOT, the comparison operators replace IS NOT.

Syntax :

The syntax for the IS NOT NULL condition in SQL is:

expression IS NOT NULL

Parameters or Arguments

expression - The expression to test for a NOT NULL value.

Note: The condition evaluates to TRUE if the expression is not a NULL value. The condition evaluates to FALSE if the value is NULL.

SELECT Statement using IS NOT NULL operator.

Example 1: Look at the following "Persons" table:

Suppose that the "Address" column in the "Persons" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.

How do we select only the records with no NULL values in the "Address" column?

We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

Output:

Example 2: Retrieve all employees from the table "Employees" where salary is NOT NULL value.

SELECT *  
FROM [javatpoint].[dbo].[Employees]  
WHERE salary IS NOT NULL;  

Example 3:

SELECT code, cd_check FROM albany
WHERE NOT cd_check <=> NULL;

Example 4: Using IS NOT NULL with the SELECT Statement

IS NOT NULL is the recommended comparison operator to use in SQL when testing for a non-NULL value. Let's start with a SELECT statement instance that explains how to employ the IS NOT NULL condition.

In this example, we have a table called products with the following data:

Enter the following SQL statement:

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

This example will return all records from the products table where the customer_id does not contain a NULL value.

The NOT NULL constraint is a column constraint that assures that no NULL values are stored in a column.

The SQL Server NOT NULL restrictions simply state that a column cannot be NULL.

The NOT NULL operator negates the result of the NULL operator to define a NOT NULL constraint for a column.

If you don't define the NOT NULL constraint, SQL Server will allow NULL values in the column by default.

It's worth noting that NOT NULL constraints are always expressed as column constraints.

Syntax:

To control whether a column can accept NULL, you use the NOT NULL constraint:

CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);

Any attempt to insert or update NULL in a column that has a NOT NULL constraint will result in an error.

Example 1: The following example creates a table with NOT NULL constraints for the columns: first_name, last_name, and email:

CREATE SCHEMA hr;
GO

CREATE TABLE hr.persons(
  person_id INT IDENTITY PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20)
);

In this example, the phone column can accept NULL.

Example 2: Declaring NOT NULL columns

With the not-null constraints, the following CREATE TABLE statement generates a new table called invoices.

CREATE TABLE invoices(
  id SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  qty numeric NOT NULL CHECK(qty > 0),
  net_price numeric CHECK(net_price > 0) 
);

The NOT NULL keywords that follow the data type of the product id and qty columns are used to specify NOT NULL constraints in this instance.

Multiple constraints, such as NOT NULL, check, unique, and foreign key, can occur next to each other in a column. The order in which the limitations are applied is irrelevant. The constraint in the list can be checked in any order by PostgreSQL.

The column will allow both NULL and non-NULL values if you use NULL instead of NOT NULL. It will take NULL by default unless you explicitly specify NULL or NOT NULL.

Example 3: IS NOT NULL is the recommended comparison operator to use in SQL when testing for a non-NULL value. Let's start with a SELECT statement illustration that explains how to employ the IS NOT NULL condition.

In this example, we have a table called products with the following data:

Enter the following SQL statement:

SELECT *
FROM products
WHERE category_id IS NOT NULL;

There will be 6 records selected. These are the results that you should see:

This example will retrieve all records from the products table that do not have a NULL value for customer_id.

In SQL Server, you can add a NOT NULL constraint.

Not null constraints are an excellent method to add additional layer of data validation.

Use the ALTER TABLE.. ALTER COLUMN command to enforce NOT NULL for a column in SQL Server, and then restate the column description with the NOT NULL attribute.

The ALTER TABLE command can also be used to apply the NOT NULL constraint to an existing table field.

Example 1: The NOT NULL constraints for columns are often defined when the table is created. However, there are situations when we want to make a column that takes NULL values not allow NULL values.

To carry the change, we use these two steps:

First, update all current NULL values to non-NULL values using the UPDATE statement.

UPDATE table_name
SET column_name = 0
WHERE
column_name IS NULL;

To discover the rows with a NULL column name, we utilise the IS NULL operator in the WHERE clause.

Second, use the ALTER TABLE statement to add the NOT NULL constraint to the column:

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;

Let's say the training table's taken date field is NULL and we want to make it NOT NULL.

Example 2: For example,

SQL Server

ALTER TABLE Customers
ALTER COLUMN age INT NOT NULL;

The NOT NULL constraint is added to the column college_id in an existing table with this SQL query.

Oracle

ALTER TABLE Customers
MODIFY age INT NOT NULL;

MySQL

ALTER TABLE Customers
MODIFY COLUMN age INT NOT NULL;

PostgreSQL

ALTER TABLE Customers
ALTER COLUMN age SET NOT NULL;

CASE With Not Null

The query is as follows:

select case
when Name is not null then Name else Age
end as NotNULLValue
from SelectNotNullColumnsDemo;

Output:

The ALTER TABLE... CHANGE command in MySQL can also be used to apply NOT NULL constraints to existing columns.

NOT NULL is a SQL constraint that prevents NULL values from being inserted into the specified column.

We cannot insert a new record in the table without raising the amount to any field that is designated as a NOT NULL constraint in the table.

Syntax:

The following syntax adds the NOT NULL constraint to the column at the time of table creation:

CREATE TABLE Table_Name  
(  
Column_Name_1 DataType (character_size of the column_1) NOT NULL,  
Column_Name_2 DataType (character_size of the column_2) NOT NULL,  
Column_Name_3 DataType (character_size of the column_3) NOT NULL,  
........,  
Column_Name_N DataType (character_size of the column_N) NOT NULL,  
)  ;  

A NOT NULL constraint can be applied to one or more columns in a SQL table.

When the table currently exists, the following code adds the NOT NULL constraint to the column:

ALTER TABLE Table_Name ALTER COLUMN Column_Name datatype NOT NULL; 

Example: Here’s an example of SQL query to add NOT NULL constraint in MySQL.

ALTER TABLE product_sales
     CHANGE
     order_date
     order_date DATE NOT NULL;

describe product_sales;

Output:

In the above query, we have added NOT NULL constraint to column order_date.

Difference between SQL NOT NULL Constraint and SQL Unique Constraint

SQL NOT NULL

• This is a limitation on columns. A not-null constraint cannot be defined with any name.
• We can offer the same value in the same column many times due to the SQL NOT NULL constraint.
• The SQL Unique constraint prevents the same value from being repeated in the same column.
• A NOT NULL constraint prevents NULL values from being placed into a specific column.
• NOT NULL constraints can be applied during table creation or after the table has been created using the ALTER statement.
• A NOT NULL constraint is a column constraint that cannot be applied to a table.
• The NULL constraint is the inverse of the NOT NULL constraint, although it is not necessary to specify NULL when creating a table; the default is NULL, which does not mean that the column must have NULL, but rather that it may contain NULL value.

SQL UNIQUE

• The SQL UNIQUE constraint guarantees that the values inserted into a table's column or field are identical.
• A unique constraint prevents several rows from containing the same value in the same column or set of columns, although it does allow for some null values.
• An index on a column or collection of columns is automatically created when a UNIQUE constraint is added.
• A group of one or more table fields/columns that uniquely identify a record in a database table is referred to as a unique key.
• When more than one row for a column or combination of columns has been utilised as a unique in PostgreSQL, the UNIQUE constraint is broken.
• in a table constraint Two NULL values for the same column in different rows are distinct and do not break the UNIQUE constraint's distinctiveness.

Example: The GROUP BY Clause and NULL Values

Let's start by executing a simple SQL query with both the GROUP BY clause and NULL values:

SELECT department
FROM employee
GROUP BY department;

RESULTS:

Note: For clarity, I've included a numbered list here; normally, the results would be displayed as an unnumbered list.

The first result value is a NULL, which is represented by an empty string (the empty line before the IT department). This empty area reflects all of the NULL values returned by the GROUP BY clause, indicating that NULLs are treated as legitimate values by GROUP BY.

In the next query, we will count how many employees are in each department, including the "NULL" department:

SELECT department, count(*)
FROM employee
GROUP BY department;

RESULTS:

Note: I've added a numbered list here for clarity; usually the results would be shown as an unnumbered list.

We can deduce that all NULL values are gathered into one value or bucket based on the preceding results from a "GROUP BY" perspective. This gives the impression that NULL is a single department with two staff. However, addressing NULLs in this manner — putting several NULLs into a single bucket - contradicts the idea that a NULL value is not equal to any other value, even another NULL.

We need to go over the SQL standard to understand why NULLs are grouped together. "Any two values that are equivalent to one another, or any two NULLs," according to SQL, are "not distinct." When the GROUP BY clause (or other grouping keywords) is used, SQL can use this definition of "not distinct" to group and sort NULLs.

Primary Key

• Primary keys can only save unique values across tables and cannot store NULL.
• A table can have only one primary key, but it can also contain numerous unique keys.
• There can only be one PRIMARY KEY Column per table.
• Other tables can use the primary key as a foreign key.

Not Null

• This is a limitation on columns. To create a not-null constraint, no name can be defined.
• We can offer the same value in the same column many times using the SQL NOT NULL constraint.
• The SQL Unique constraint prevents the same value from being repeated in the same column.
• A NOT NULL constraint prevents NULL values from being placed into a specific column.
• NOT NULL constraints can be applied during table creation or after the table has been created using the ALTER statement.
• A NOT NULL constraint is a column constraint that cannot be applied to a table.

As we saw in the previous part, SQL NOT NULL Constraint SQL Alter table is used to modify column structure, and the same query is used to eliminate SQL NOT NULL Constraint SQL Alter table,

Syntax:

1. We don't need to check if there is data in that column when eliminating the SQL NOT NULL constraint

ALTER [ COLUMN ] [ SET | DROP ] NOT NULL

Here are the steps to remove NOT NULL constraint in MySQL.

2. Here is the syntax of ALTER TABLE statement to remove Not Null constraint.

alter table table_name full_column_definition;

You must give the table name and the full declaration of the column whose NOT NULL constraint you want to delete in the SQL query above. The NOT NULL constraint is not specified in the column definition.

Example 1: let us say you have a table sales(id, order_date, amount)

create table sales (   
   id int primary key,   
   order_date date,
   amount int not null
);

Here is the SQL query to remove NOT NULL constraint from amount column.

alter table sales amount int;

We specify amount column’s full definition, but without NOT NULL constraint.

Example 2: postgresql remove not null constraint

alter table users alter column email drop not null;

Example 3: removing SQL NOT NULL Constraint

In the last example, we utilised an employee table with a SQL NOT NULL constraint on field emp_name, therefore to remove it, we used the SQL edit table statement.

Alter table tblemp 
Alter column Emp_name varchar(20) NULL

Example 4: Remove NOT NULL constraint

If we want to remove the NOT NULL constraint from the city column in orders table the following SQL can be used:

ALTER TABLE orders ALTER COLUMN city DROP NOT NULL;