NOT NULL Constraint in SQL Server(TSQL)

 

We can use “NOT NULL Constraint” to prevent values in a column from containing null data.

Example:

In the following example, when creating a table, we set the NOT NULL Constraint to the [Name] column and add a few records to the table.

When we try to add data with a NULL value to the [Name] column that we have defined as “NOT NULL”, we receive the following error.

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.

The statement has been terminated.

Drop the table and recreate it as follows. So the [NAME] column can contain NULL values. Then add NULL data to the table with the script below.

Then, when we try to add NOT NULL Constraint to the [NAME] column with the following command, we will get an error again. Because the [Name] column contains NULL data.

Msg 515, Level 16, State 2, Line 12

Cannot insert the value NULL into column ”, table ”; column does not allow nulls. UPDATE fails.

The statement has been terminated.

After you have updated the NULL data, “ALTER TABLE …” command will be completed successfully.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *