How to Alter Column from Null to Not Null in SQL Server Table - SQL Server / T-SQL Tutorial Part 52

Scenario:

You are working as SQL Server developer with a Bank. They have Dbo.Customer table in TechBrothersIT database with below definition.

Create Table dbo.Customer
(FirstName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)
)

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?

Solution:

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 'TechBrothersIT.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 

update dbo.Customer
set LastName=''
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



2 comments:

  1. That's useful, thanks! I can say that I've been struggling with file and document management for a while, so I hope it can be quite beneficial for me.

    ReplyDelete
  2. There are many solutions on the internet that can help you with that. If you need useful software for business, the pandadoc service offers an intuitive interface for creating electronic refusal forms. You can check out https://www.pandawaiver.com/blog/2022/11/25/how-to-write-a-hold-harmless-agreement/ and learn more about it. I can easily add the required fields, text and signatures to get a fully customisable and professional document. This allows me to create waiver forms to suit my unique requirements and needs.

    ReplyDelete