SQL NOT NULL Constraint

The NOT NULL in sql constraint helps our data to become free of unknown or missing values.

By default in SQL, our columns can hold NULL values, i.e., the value in the specified columns might be missing or unknown. But there can be a different instance where we do not want any column to have a null value. What do we do in such a situation? We use the NOT NULL constraint.

One common reason for using the NOT NULL constraint is that we do not want missing or unknown values.

Syntax:

We can use the not null constraint in many cases. This article will cover the usage with two different clauses :

1. CREATE TABLE
2. ALTER TABLE

Demo Database:

Consider a table called "Cities" that contains information about numerous cities.This table has columns for city ID, name, population, and country. ⁤To apply the NOT NULL in sql, some fields must always contain a value. ⁤

• CityID (Unique identifier for each city, with a NOT NULL constraint to ensure every city has an ID)
• CityName (Name of the city, also with a NOT NULL constraint to ensure every city is named)
• Population (Population of the city, with a NOT NULL constraint to ensure the population is recorded)

Here's how the SQL statement to create this table with the not null in sql constraints might look:

Let's perform some operations on this table to demonstrate how the not null in sql constraint works in practice.

1. Inserting Data

We will insert data into the "Cities" table. Note that all fields must be provided due to the NOT NULL constraint.

2. Attempting to Insert Null Values Let's see what happens if we try to insert a record with a NULL value in a NOT NULL column.

This statement will result in an error because the CityName cannot be NULL.

3. Updating Data with Null If we attempt to update an existing city's name to NULL, it would also result in an error due to the NOT NULL constraint.

4. Querying Data Finally, we can query the data to see what's in our table.

This will list all the cities along with their IDs and populations, but none of the fields will be NULL due to the constraints we've applied.

These operations demonstrate the enforcement of NOT NULL constraints in a SQL database, ensuring data integrity by preventing missing values in essential fields.

SQL NOT NULL on CREATE TABLE:

Let us look at the syntax of the NOT NULL in sql with the CREATE TABLE command:

Using the syntax defined above, let us use it to create the GRADES table:

Query:

If you were to describe this table structure using a DESC command, you'd expect to see an output indicating that CityID, CityName, and Population cannot be NULL. Here’s a simplified view of what that might look like:

SQL NOT NULL on ALTER TABLE

Query:

Suppose you initially created the "Cities" table without the NOT NULL constraint on the Population field and now you want to alter the table to enforce that the Population must always be specified. You would use the following SQL command:

We use the ALTER TABLE clause. The ALTER TABLE statement is used to add, delete, or modify a column in our table.

The syntax for altering our column and adding the not null constraint is:

Use the following query to add the NOT NULL constraint to the NAME column: Query

Output

After executing the above ALTER TABLE command, the Population column is altered to disallow NULL values. Describing the table structure now would show the "Null" field for Population as "NO", indicating that it must have a value:

This output reflects the change in the table's structure, with the Population column now having a NOT NULL constraint.

It sums up the two ways to add the not null constraint in SQL.