You may find yourself needing the change the definition of a table or column from time-to-time while working as a database professional.
One change you may need to make is changing a column from nullable to non nullable. It may have been decided that we actually don’t want NULL to appear in a column after all.
In this very brief tutorial, we’ll discuss how to change a column from allowing NULL to disallowing NULL.
Run an ALTER COLUMN statement within an ALTER TABLE statement
Take a look at the following Books table:
We can see the nullability of all the columns of this table within the Object Explorer:
The Title column is presently nullable, meaning it’s possible for NULL to be inserted into that column. Let’s say we want to change that, and start disallowing NULL from appearing in that column.
To accomplish that, we need to write an ALTER COLUMN statement, which is within an ALTER TABLE statement:
If we refresh our column list in the Object Explorer, we see that the Title column is set to not null:
Notice we needed to completely redefine the column
Notice that when we changed our column to NOT NULL, we had to completely redefine the column. Not only did we outline the new nullability setting, but we also needed to outline the data type of the column. SQL Server does not have a way for you to only change this or that. We had to redefine everything about the column!
If there are other unique properties and settings on the column, you need to make sure you outline them in your ALTER COLUMN statement to preserve those settings.
What if the column already contains NULL?
There may be an instance where you need to make a column non-nullable that already contains NULL values. If we try to make a column non-nullable while there are presently NULL values in said column, we’ll get an error message. Here’s an example of trying to change the Author column, which contains a NULL value, to NOT NULL:
Before we can modify this column to NOT NULL, we need to change those rows that presently contain NULL to contain something else. Luckily, it’s very easy to locate rows that contain NULL using the IS NULL predicate:
Now that there aren’t any NULL values in the column, we can run our ALTER COLUMN statement successfully:
So simple!
Next Steps:
Leave a comment if you found this tutorial helpful!
Make sure you aren’t making these 7 mistakes when it comes to NULL!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
