Friday , October 4 2024

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.

Loading

About 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 *

Categories