Back to articles list March 24, 2021 - 8 minutes read What Is a NOT NULL Constraint in SQL? Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: sql learn sql Data Engineering Constraints SQL has several ways of dealing with NULLs. In this article, we’ll focus on the NOT NULL constraint and other NULL-related clauses. Understand this and you’ll take an important step in your SQL growth! Before we get straight into the NOT NULL constraint, let’s quickly define what NULLs and constraints are. NULL values are a central concept in SQL databases. The idea behind a NULL value is simple: a NULL value means that we do not have a known value for that field. SQL databases use constraints as rules that define what values can be stored in a column. There are different types of constraints: primary keys, check constraints, unique constraints, and NOT NULL constraints. Such tools ensure the accuracy and reliability of the data in the database. In this article, we will cover the NOT NULL constraint, which is used to avoid having NULL values in a column. Remember, a NULL value in a column represents the absence of a defined value. If for some reason we omit the value at the time of record creation, the field by default is set to NULL, meaning that NULL is the default value for the column. If this seems a little too advanced for your current level of SQL, I’d like to suggest our SQL Constraints course. It will give you hands-on experience in creating and altering constraints in a relational database. Why Use NULL Values? What situations call for a NULL value? Perhaps we don’t know the value when we create a record. For example, suppose we have the table person with the column birth_date. We need to create a new record, but we don’t know the person’s birth_date yet. So, we can put a NULL value into the column birth_date and enter the actual value when we get it. The NULL is a placeholder for a missing value. Another possibility is when the value for that column doesn’t exist. Continuing our person table example, suppose there is a column that stores the person’s driver_license_number. However, not everyone has a driver’s license, so this column should be nullable. Here, we use the NULL value to represent the value’s non-existence. In other scenarios, we must avoid NULL values. There can be different reasons for that; sometimes the business meaning of the column doesn’t allow a missing value or sometimes we have a primary key on a column, which means this column will not accept NULL values. (All the values in a primary key column have to be unique and non-NULL.) Below we have some business scenarios where we need to use a NOT NULL constraint: TableColumnReason to avoid a NULL value personsocial_security_numberEvery person must have a social security number (i.e. for tax, insurance, and ID reasons). movietitleEvery movie must have a title. We don’t allow the creation of a record in the movie table with a NULL title. bookISBNThe ISBN (a unique ID number for books) is usually the primary key for this kind of table; as a primary key, this value is by definition NOT NULL. doctorlicense_numberAll doctors must have a license number to practice. airportairport_codeEvery airport has a mandatory 3 letter code. How to Define a NOT NULL Constraint in a Table So, we’ve established that some columns should not ever contain a NULL, either because they’re part of a primary key or because of business reasons. Primary key columns are non-nullable by default, but in other cases we can use the NOT NULL constraint to avoid having any NULL values. NOT NULL constraints are defined at the table column level, as we can see in the following example: CREATE TABLE person ( social_security_number INTEGER NOT NULL, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50), birth_date DATE, driver_license_number VARCHAR(12) ) In the above table creation statement, we can see two NOT NULL constraints on the columns social_security_number and last_name. If we omit the NOT NULL after the column definition (like we did for the first_name column) then the column will allow NULL values; moreover, the default value for this column will be NULL. How a NOT NULL Constraint Works In this section, we will demonstrate some SQL code used to create records with NULL values. For example, with the following INSERT we’ll create a record in the person table with NULL values in the columns birth_date and driver_license_number: INSERT INTO person ( social_security_number, last_name, first_name, birth_date, driver_license_number ) VALUES ( 1234567, ‘Smith’, ‘John’, NULL, NULL ); Next, we’ll show how to create the test table and how to insert records for two tests. As these tests will be held on a future date, the test result is not yet known. Thus, we’ll set the column test_result to NULL; this will represent the missing value for the test result. CREATE TABLE test ( test_id INTEGER, planned_date DATE, Test_result BOOLEAN ); INSERT INTO test ( test_id, planned_date, test_result ) VALUES ( 10021, ‘2021-06-15’, NULL); INSERT INTO test ( test_id, planned_date) VALUES ( 10022, ‘2021-07-15’); Did you notice that we used two different ways to insert a record with a NULL value? The first INSERT uses an explicit NULL. The second INSERT omits the test_result column and its value; by default, a NULL is assigned to the test_result column field for that row. What if we have a contact table but we don’t have all the expected information when we create a record for a new contact? In the example below, we show the creation of the table contact and how we can insert a record for a contact who didn’t provide an email address: CREATE TABLE contact ( contact_last_name VARCHAR(50), contact_first_name VARCHAR(50), contact_phone VARCHAR(50), contact_email VARCHAR(50), ); INSERT INTO contact ( contact_last_name, contact_last_name, contact_phone, contact_email ) VALUES ( ‘Doe’, ‘John’, ‘341-987-7652’, NULL); Inserting a NULL Into a NOT NULL Column When are NOT NULL constraints checked? Every time an INSERT or UPDATE is executed against an SQL table, all the NOT NULL constraints in the affected table are checked. As an example, let’s see what happens when we try to INSERT a record with a NULL value into a column with a NOT NULL constraint: As an example, let’s see what happens when we try to INSERT a record with a NULL value into a column with a NOT NULL constraint: INSERT INTO person ( social_security_number, last_name, first_name, birth_date, driver_license_number ) VALUES ( 1234567, NULL, ‘John’, ‘1994-03-03’,’23232323’); We tried to put a NULL value in the column last_name, which is non-nullable. The database returned this error: ERROR: null value in column “last_name” violates not-null constraint We can obtain a similar error if we try to modify the value in a column with a NOT NULL constraint to NULL: UPDATE person SET last_name = NULL WHERE last_name = ‘Smith’; And the error returned by the database is the same: ERROR: null value in column “last_name” violates not-null constraint For readers who want to go deeper with SQL constraints, I suggest the article What is a SQL constraint. You’ll learn the purposes and benefits of the most common SQL constraints. Using Logical Conditions with NULL Values In this section, we’ll describe some common situations related to NULL values and logical conditions. One of the most common errors when working with NULL values is trying to identify NULL values using an equality condition: WHERE driver_license_number = NULL The previous condition is wrong because we can’t use the ‘=’ operator against the NULL value – a very frequent mistake. The correct way to identify a NULL value is using the ‘IS NULL’ operator; the correct WHERE clause is: WHERE driver_license_number IS NULL We can also use the opposite operator, IS NOT NULL, to test for the opposite condition: WHERE driver_license_number IS NOT NULL Another important point is the value returned by the logical condition when one of the operand values involved is a NULL. Usually logical conditions return as TRUE or FALSE; however, when NULL is involved, a new possible value called UNKNOWN appears. Suppose we want to get records for people who were born in this century. We can use a simple WHERE clause like WHERE birth_date >= ‘2000-01-01’. But what can we conclude about persons having a NULL in birth_date ? ConditionValue of birth_dateCondition Result birth_date >= ‘2000-01-01’‘1994-06-03’FALSE birth_date >= ‘2000-01-01’‘2018-09-13’TRUE birth_date >= ‘2000-01-01’NULLUNKNOWN In the above table, we can see the UNKNOWN value returned when the condition is “birth_date >= NULL”. This is reasonable – if we don’t know when this person was born, then UNKNOWN is a perfect result for this case. For those readers who want to go a step further with NULL values, I suggest the article Understanding the Use of NULL in SQL Three-Valued Logic. Want to Learn More About SQL Constraints? In this article, we explained SQL’s NOT NULL constraints – how they work and how we can create a NOT NULL constraint. We also covered some points related to NULL values and logical conditions. If you’re interested in learning how to create and manage tables with various constraints, I recommend the Creating Database Structure course. The articles What Is a Primary Key in SQL and What Is a Foreign Key in SQL are also great explanations of some of SQL’s basic constraints. Check them out, continue learning, and increase your SQL skills! Tags: sql learn sql Data Engineering Constraints You may also like What is a Primary Key in SQL? Get to know one of the most important concepts in SQL databases: primary keys. It only takes 10 minutes to learn the basics! Read more What Is a Unique Constraint in SQL? In this article, we delve into the basics of SQL’s UNIQUE constraint. We present its implementation on table column(s) and its usage scenarios. Read more What Is a SQL Constraint? This article introduces the concept of SQL constraints. The most common SQL constraints are explained along with their application. Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more What Is a Foreign Key in SQL? Do you know what a SQL foreign key is? Read this article to find out how they work and how they are related to primary keys. Read more What is a Primary Key in SQL? Get to know one of the most important concepts in SQL databases: primary keys. It only takes 10 minutes to learn the basics! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.