Hello everyone,

We have come across one difficult situation where we have to put one extra field in Sql Server database where it must be NOT NULL and should not affect existing records.

First thing comes in mind that if we are adding new field in existing table it will definitely change some behavior if we set it as a NOT NULL because when we make change in table by adding new field (NOT NULL) it will not allow to add new field with NOT NULL because we are setting NOT NULL and without NULL what it would expect to enter the value.

So to overcome this problem we have to tell Sql Server that we are adding new field with NOT NULL property but we also adding default value for that then it will allow us to change the configuration of table.

First of all write the query to add the column with constraint to set it’s default value and then in next line remove that constraint.

To do that just follow the steps below:

ALTER TABLE [dbo].[TableName] ADD FieldName DataType NOT NULL CONSTRAINT DF_MyTable_MyColumn DEFAULT 0

ALTER TABLE [dbo].[TableName] DROP CONSTRAINT DF_MyTable_MyColumn

(Change above script as per your configuration / requirement)

Now we have the field without affecting our existing records of a table.

Here I have set 0 as a default value. You can set any value whatever datatype you have assigned to the field.

Thanks and Enjoy 🙂