Db2 NOT NULL

Summary: in this tutorial, you will learn how to use the Db2 NOT NULL constraint to prevent NULL values stored in a column.

Db2 NOT NULL constraint overview

In the database world, NULL is a marker or special value that indicates the missing information or the information is not applicable.

To avoid NULL to be stored in a column, you use the NOT NULL constraint with the following syntax:

column_name type NOT NULL
Code language: SQL (Structured Query Language) (sql)

Unlike primary key and foreign key constraints, the NOT NULL constraints are the column constraints, therefore, they must appear in the column definitions of a table.

If you declare a column without specifying the NOT NULL constraint, Db2 will assume that the column accepts NULL values. To declare a column that explicitly accepts NULL values, you use the following syntax:

column_name type NULL
Code language: SQL (Structured Query Language) (sql)

Db2 NOT NULL constraint example

The following statement creates a new table named discounts that stored discounted books:

CREATE TABLE discounts (
	book_id INT NOT NULL,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	percentage DECIMAL(4,2) NOT NULL,
	description VARCHAR(50),
	PRIMARY KEY(book_id, start_date, end_date),
	FOREIGN KEY(book_id)
		REFERENCES books(book_id)
			ON UPDATE RESTRICT
			ON DELETE CASCADE
);
Code language: SQL (Structured Query Language) (sql)

In the discounts table, all the columns have NOT NULL constraints except for the description column.

Even though the book_id, start_date, end_date are the primary key columns, you need to include the NOT NULL constraint because the primary key does not accept NULL.

Typically, in other database systems, when you define the primary key of a table, all primary key columns implicitly have the NOT NULL constraints.

Adding NOT NULL constraint to an existing column

Sometimes, you may need to make a nullable column NOT NULL. To do this follow these steps:

First, update the value of the column to non-NULL values:

UPDATE 
	table_name
SET 
	column_name = non-null value
WHERE 
	column_name IS NULL;
Code language: SQL (Structured Query Language) (sql)

Then, modify the column to add theNOT NULL constraint:

ALTER TABLE table_name
ALTER COLUMN column_name 
SET NOT NULL;
Code language: SQL (Structured Query Language) (sql)

For example, to make the description column of the discounts table NOT NULL, you use these steps:

First, update the description to blank if the value in this column is NULL:

UPDATE 
	discounts
SET 
	description = ''
WHERE 
	description IS NULL;
Code language: SQL (Structured Query Language) (sql)

Then, modify the description column using the following ALTER TABLE statement:

ALTER TABLE discounts 
ALTER COLUMN description 
SET NOT NULL;
Code language: SQL (Structured Query Language) (sql)

Removing the NOT NULL constraint

You can use the following ALTER TABLE ALTER COLUMN statement to remove the NOT NULL constraint from a column:

ALTER TABLE table_name
ALTER COLUMN column_name 
DROP NOT NULL;
Code language: SQL (Structured Query Language) (sql)

The following statement removes the NOT NULL constraint from the description column of the discounts table:

ALTER TABLE discounts
ALTER COLUMN description
DROP NOT NULL;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 NOT NULL constraint to prevent NULL values from storing in a column.