NOT NULL Constraints in Microsoft SQL Server

Ensure that the proper amount of data has been entered

Printed database schema
slungu / Getty Images

NOT NULL constraints in Microsoft SQL Server specify that a column may not contain NULL values.

Null is different from a zero or a zero-character string. Null means that no entry has been made. 

When you create a new NOT NULL constraint on a database column, SQL Server checks the column’s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint. All future INSERT or UPDATE commands that would cause the existence of a NULL value will fail to commit the transaction.

Creating a NOT NULL Constraint

There are many ways you can create a UNIQUE constraint in SQL Server. If you wish to use Transact-SQL to add a UNIQUE constraint on an existing table, you may use the ALTER TABLE statement, as illustrated below:

ALTER TABLE
ALTER COLUMN NOT NULL

If you prefer to interact with SQL Server using GUI tools, you may also create a NOT NULL constraint using SQL Server Management Studio. Here’s how:

  • Open SQL Server Management Studio.
  • Expand the Tables folder of the database where you wish to create the constraint.
  • Right-click the table where you wish to add the constraint and click Design.
  • Check the NOT NULL checkbox for the column(s) that you wish to have ​NOT NULL constraint(s).
Format
mla apa chicago
Your Citation
Chapple, Mike. "NOT NULL Constraints in Microsoft SQL Server." ThoughtCo, Dec. 6, 2021, thoughtco.com/not-null-constraints-1019824. Chapple, Mike. (2021, December 6). NOT NULL Constraints in Microsoft SQL Server. Retrieved from https://www.thoughtco.com/not-null-constraints-1019824 Chapple, Mike. "NOT NULL Constraints in Microsoft SQL Server." ThoughtCo. https://www.thoughtco.com/not-null-constraints-1019824 (accessed March 19, 2024).