{"id":530,"date":"2016-04-12T06:29:06","date_gmt":"2016-04-12T06:29:06","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=530"},"modified":"2025-01-23T19:57:44","modified_gmt":"2025-01-24T02:57:44","slug":"sql-not-null-constraint","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-not-null-constraint\/","title":{"rendered":"SQL NOT NULL Constraint"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the SQL NOT NULL<\/code> constraint to ensure a column cannot have NULL<\/code>.<\/p>\n\n\n\n

Introduction to SQL NOT NULL constraint #<\/a><\/h2>\n\n\n\n

In SQL, NULL<\/a><\/code> means unknown value or missing data. If you define a table column without using any constraint, it will accept NULL<\/code> by default.<\/p>\n\n\n\n

To ensure that the column cannot have NULL<\/code>, you can use the NOT NULL<\/code> constraint. <\/p>\n\n\n\n

The NOT NULL<\/code> constraint prevents inserting<\/a> or updating<\/a> NULL<\/code> into a specified column. It is helpful for defining important columns that should never be NULL<\/code>.<\/p>\n\n\n\n

To apply a NOT NULL<\/code> constraint to a column, you use the following syntax:<\/p>\n\n\n

column_name datatype NOT NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> candidates (\n  first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  email VARCHAR<\/span>(512<\/span>) NOT<\/span> NULL<\/span>,\n  phone VARCHAR<\/span>(25<\/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 candidates<\/code> table includes four columns with NOT NULL<\/code> constraints. It means you cannot insert NULL<\/code> into any of this column.<\/p>\n\n\n\n
The following statement will fail because it attempts to insert NULL<\/code> into the email<\/code> and phone<\/code> columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  candidates (first_name, last_name, email, phone)\nVALUES<\/span>\n  ('John'<\/span>, 'Doe'<\/span>, NULL<\/span>, NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the following insert<\/a> statement succeeds because it provides valid values for all the NOT NULL<\/code> columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  candidates (first_name, last_name, email, phone)\nVALUES<\/span>\n  (\n    'John'<\/span>,\n    'Doe'<\/span>,\n    'john.doe@gmail.com'<\/span>,\n    '(408)-111-2222'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Adding a NOT NULL constraint to an existing column #<\/a><\/h2>\n\n\n\n
Typically, you add a NOT NULL<\/code> constraint to a column when creating the table<\/a>. Sometimes, you may want to apply a NOT NULL<\/code> constraint to an existing nullable column.<\/p>\n\n\n\n
To do that, you need to follow these steps:<\/p>\n\n\n\n
First, update NULL<\/code> to a non-NULL value using the UPDATE<\/code> statement:<\/p>\n\n\n
UPDATE<\/span> table_name\nSET<\/span>\n  column_name = non_null_value\nWHERE<\/span>\n  column_name IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, add the NOT NULL<\/code> constraint to the column using the ALTER TABLE<\/a> statement<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nMODIFY<\/span> column_name data_type NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example shows how to add a NOT NULL<\/code> constraint to an existing column:<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> interviews (\n  candidate_id INT<\/span> NOT<\/span> NULL<\/span>,\n  hiring_manager VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  interview_date TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, add the NOT NULL<\/code> constraint to the interview_date<\/code> column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> interviews \nMODIFY<\/span> interview_date TIMESTAMP<\/span> NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
First, create a table<\/a> called interviews<\/code> to store interviews:<\/p>\n\n\n
Note that we use the IS NULL<\/a> operator in the WHERE clause<\/a> to find the rows with values in the column column_name<\/code> are NULL<\/code>.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
For example, the following creates a table<\/a> called candidates<\/code> table to store candidates:<\/p>\n\n\n