{"id":596,"date":"2019-06-04T20:06:34","date_gmt":"2019-06-05T04:06:34","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=596"},"modified":"2020-04-11T06:58:51","modified_gmt":"2020-04-11T14:58:51","slug":"db2-not-null","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-not-null\/","title":{"rendered":"Db2 NOT NULL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 NOT NULL<\/code> constraint to prevent NULL<\/code> values stored in a column.<\/p>\n\n\n\n

Db2 NOT NULL constraint overview<\/h2>\n\n\n\n

In the database world, NULL is a marker or special value that indicates the missing information or the information is not applicable.<\/p>\n\n\n\n

To avoid NULL<\/code> to be stored in a column, you use the NOT NULL<\/code> constraint with the following syntax:<\/p>\n\n\n

column_name type NOT NULL\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Unlike primary key<\/a> and foreign key<\/a> constraints, the NOT NULL<\/code> constraints are the column constraints, therefore, they must appear in the column definitions of a table.<\/p>\n\n\n\n
If you declare a column without specifying the NOT NULL<\/code> constraint, Db2 will assume that the column accepts NULL<\/code> values. To declare a column that explicitly accepts NULL<\/code> values, you use the following syntax:<\/p>\n\n\n
column_name type NULL\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 NOT NULL constraint example<\/h3>\n\n\n\n
The following statement creates a new table<\/a> named discounts<\/code> that stored discounted books:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> discounts (\n\tbook_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tstart_date DATE<\/span> NOT<\/span> NULL<\/span>,\n\tend_date DATE<\/span> NOT<\/span> NULL<\/span>,\n\tpercentage DECIMAL<\/span>(4<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\n\tdescription VARCHAR<\/span>(50<\/span>),\n\tPRIMARY KEY<\/span>(book_id, start_date, end_date),\n\tFOREIGN<\/span> KEY<\/span>(book_id)\n\t\tREFERENCES<\/span> books(book_id)\n\t\t\tON<\/span> UPDATE<\/span> RESTRICT\n\t\t\tON<\/span> DELETE<\/span> CASCADE<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the discounts<\/code> table, all the columns have NOT NULL<\/code> constraints except for the description<\/code> column.<\/p>\n\n\n\n
Even though the book_id<\/code>, start_date<\/code>, end_date<\/code> are the primary key columns, you need to include the NOT NULL<\/code> constraint because the primary key does not accept NULL.<\/p>\n\n\n\n
Typically, in other database systems, when you define the primary key of a table, all primary key columns implicitly have the NOT NULL<\/code> constraints.<\/p>\n\n\n\n
Adding NOT NULL constraint to an existing column<\/h2>\n\n\n\n
Sometimes, you may need to make a nullable column NOT NULL<\/code>. To do this follow these steps:<\/p>\n\n\n\n
First, update the value of the column to non-NULL values:<\/p>\n\n\n
UPDATE<\/span> \n\ttable_name\nSET<\/span> \n\tcolumn_name = non-null<\/span> value<\/span>\nWHERE<\/span> \n\tcolumn_name IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, modify the column to add theNOT NULL<\/code> constraint:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> COLUMN<\/span> column_name \nSET<\/span> NOT<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, to make the description<\/code> column of the discounts<\/code> table NOT NULL<\/code>, you use these steps:<\/p>\n\n\n\n
First, update the description<\/code> to blank if the value in this column is NULL:<\/p>\n\n\n
UPDATE<\/span> \n\tdiscounts\nSET<\/span> \n\tdescription = ''<\/span>\nWHERE<\/span> \n\tdescription IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, modify the description<\/code> column using the following ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> discounts \nALTER<\/span> COLUMN<\/span> description \nSET<\/span> NOT<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Removing the NOT NULL constraint<\/h2>\n\n\n\n
You can use the following ALTER TABLE ALTER COLUMN<\/a><\/code> statement to remove the NOT NULL<\/code> constraint from a column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> COLUMN<\/span> column_name \nDROP<\/span> NOT<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement removes the NOT NULL<\/code> constraint from the description<\/code> column of the discounts<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> discounts\nALTER<\/span> COLUMN<\/span> description\nDROP<\/span> NOT<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Db2 NOT NULL<\/code> constraint to prevent NULL<\/code> values from storing in a column.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t