MySQL NULL Values

Overview

In this tutorial, we will learn about MySQL NULL values. NULL/NOT NULL are column options. We can define the columns with the options during the initial table creation process.

NULL

NULL is an SQL keyword used to denote data types as allowing a missing or absent value. The concept of NULL can actually have several meanings such as; ” no value”, “unknown value”, “missing value”, “not applicable”, “none of the above”, etc.

The most common for NULL is unknown and not applicable.

In the beginning stages of the database design, some data may not be available for all columns. These are the cases to scrutinize and determine whether undefined values should be allowed. This can also be changed for an existing table if a problem is detected due to the “nullness” of the column.

Example

In this example, we will look at the Country table from the MySQL world database. Some columns in the table are defined as NULL by default.

IndepYear, LifeExpectancy,GNP, GNPOld, HeadOfState,Capital are defines as NULL.

MySQL NULL Column Country

NOT NULL

A NOT NULL is used to denote that the column value cannot be NULL. The NOT NULL column is the column that will never hold nulls. This will ensure the integrity of the data along with minimizing the processes that have to be put into place to test for nulls or work with nulls.

NULL and NOT NULL are mutually exclusive options. The NOT NULL column option is a necessity for a PRIMARY KEY column.

Example

In this example, we will look at the City table from the MySQL world database. The columns in the table City are defined as NOT NULL option.

mysql> SHOW CREATE TABLE City \G
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT ”,
`CountryCode` char(3) NOT NULL DEFAULT ”,
`District` char(20) NOT NULL DEFAULT ”,
`Population` int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)