Not NULL constraint in SQL Server

The Not NULL constraint enforces a column not to accept NULL values.

By default columns are able to hold NULL values. So for a Not NULL constraint need to provide valid not null value for any insert and update operation.

Consider following examples.

CREATE TABLE consDemo

(

   ID INT NOT NULL,

   Name VARCHAR(50) NULL

)

insert into consDemo(Name) values

('Rohit')

This will throw error:

Cannot insert the value NULL into column 'ID', table 'dbo.consDemo'; column does not allow nulls. INSERT fails.

insert into consDemo(ID,Name) values

(1,'Harish Rao')

insert into consDemo(ID) values

(2)

You will see that first record will fail since we are trying to insert value only on Name column without providing value on ID column.

Other two records will be inserted since Name column is not mandatory and it will accept NULL values.

To enforce Not NULL constraint on a column make sure column does not contain any NULL values.

UPDATE consDemo SET [Name]='' WHERE [Name] IS NULL

Then modify the column name.

Alter table consDemo alter column name varchar(50) Not NULL

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint

Constraints in SQL Server

SQL Constraints are used to specify rules for the data in a table. It is used to limit the type of data while inserting into the table.

So these are predefined rules and restrictions on single or multiple columns, regarding the value allowed in the columns to maintain the integrity, accuracy and reliability of that columns data.

Different types of constraints in SQL Server that are commonly used.

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint

Click on each link for the more details with example.