{"id":605,"date":"2018-06-03T18:47:57","date_gmt":"2018-06-03T11:47:57","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=605"},"modified":"2020-04-11T20:13:23","modified_gmt":"2020-04-11T13:13:23","slug":"sql-server-not-null-constraint","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-not-null-constraint\/","title":{"rendered":"SQL Server NOT NULL Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server NOT NULL<\/code> constraint to ensure data contained in a column is not NULL<\/code>.<\/p>\n\n\n\n

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

The SQL Server NOT NULL<\/code> constraints simply specify that a column must not assume the NULL<\/code>.<\/p>\n\n\n\n

The following example creates a table<\/a> with NOT NULL<\/code> constraints for the columns: first_name<\/code>, last_name<\/code>, and email<\/code>:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> hr;\nGO\n\nCREATE<\/span> TABLE<\/span> hr.persons(\n    person_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\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>(255<\/span>) NOT<\/span> NULL<\/span>,\n    phone VARCHAR<\/span>(20<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the NOT NULL<\/code> constraints are always written as column constraints.<\/p>\n\n\n\n
By default, if you don’t specify the NOT NULL<\/code> constraint, SQL Server will allow the column to accepts NULL<\/code>. In this example, the phone column can accept NULL<\/code>.<\/p>\n\n\n\n
Add NOT NULL constraint to an existing column #<\/a><\/h2>\n\n\n\n
To add the NOT NULL<\/code> constraint to an existing column, you follow these steps:<\/p>\n\n\n\n
First, update the table so there is no NULL<\/code> in the column:<\/p>\n\n\n
UPDATE<\/span> table_name\nSET<\/span> column_name = <value<\/span>>\nWHERE<\/span> column_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
Second, alter the table to change the property of the column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> COLUMN<\/span> column_name data_type 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 add the NOT NULL<\/code> constraint to the phone column of the hr.persons table, you use the following statements:<\/p>\n\n\n\n
First, if a person does not have a phone number, then update the phone number to the company phone number e.g., (408) 123 4567:<\/p>\n\n\n
UPDATE<\/span> hr.persons\nSET<\/span> phone = \"(408) 123 4567\"<\/span>\nWHER phone IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, modify the property of the phone column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> hr.persons\nALTER<\/span> COLUMN<\/span> phone VARCHAR<\/span>(20<\/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 NOT NULL constraint #<\/a><\/h2>\n\n\n\n
To remove the NOT NULL<\/code> constraint from a column, you use the ALTER TABLE ALTER COLUMN<\/code> statement as follows:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> COLUMN<\/span> column_name data_type 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 remove the NOT NULL<\/code> constraint from the phone column, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> hr.pesons\nALTER<\/span> COLUMN<\/span> phone VARCHAR<\/span>(20<\/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 SQL Server NOT NULL<\/code> constraint to enforce a column not accept NULL<\/code>.<\/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