SQL Server: Tips and Tricks - 2 (2017)
How to Alter Column from Null to Not Null in SQL Server Table
You are working as SQL Server developer with a Bank. They have Dbo.Customer table in database with below definition.
Create Table dbo.Customer
FirstName VARCHAR(50) Not Null,
As you noticed that LastName can be Null. That is the problem. Company noticed that later and always want to have the value for LastName. You are asked to do analysis and write Alter script to Change Column from Null to Not Null. What things you will consider or suggestions you will provide?
The very first thing to consider in this scenario is to find out if any values are already inserted in the table for Last Name as Null. If that is the case, you can't really go ahead and Alter column from Null to Not Null. If you try, you will get below error.
Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'LastName', table 'TechIT.dbo.Customer';
column does not allow nulls. UPDATE fails.
It means we have to deal with Null values in this column first. You can talk with business and ask them what they would like to do for all the customer where Last Name is Null. You can find the list of customers where Last Name is Null by using below query.
Select * From dbo.Customer where LastName Is null
Business can provide different suggestions such as
· Let's update the LastName to Unknow where it is Null
· Or Update the Last Name to blank('') if it is Null
· Or Update the Last Name column to LNNP (Last Name Not Provided)
· Or they go back to customers and get their last Name to update
Depending upon the suggestions, go ahead and update the values in Last Name column. Let's say we decided to update to blank '', we can use below query to update
where LastName is Null
Now you are all set to change column in table from Null to Not Null.
Alter Table dbo.Customer
Alter Column LastName VARCHAR(50) Not Null