If you are on this page you likely got an error like the following:
Msg 4901, Level 16, State 1, Line 8 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified
The reason for this is that you are trying to add a column to a table without specifying the value for it, columns must have a value or be allowed to have null values (nullable). Think about it, what should the value be if it cannot be null and there is no value provided either? What we can do, is add the column without allowing null but providing a default value for all old rows and then when making future inserts, a value must be provided.
If we have the following table with a single row:
CREATE TABLE People (
[Id] INT PRIMARY KEY IDENTITY(1,1),
[Name] NVARCHAR(30) NOT NULL
)
INSERT INTO People (Name) VALUES ('Peter')
In the above we have a simple table called People with an Id and a Name. If we try to run the following alter statement on the table, we get the previously mentioned error:
ALTER TABLE People
Add [Lastname] NVARCHAR(50) NOT NULL
As you can see the new column does not allow null and it has no default value. Sometimes we want to create a new column with a value for all old rows, but new rows should not be allowed to be null or have a default value. There is no way to do this without adding these constraints and then removing them. However by first adding a default value (a constraint) and removing it afterwards we can achieve the same thing:
ALTER TABLE People
Add [Lastname] NVARCHAR(50) NOT NULL
CONSTRAINT People_Lastname_Temp_Constraint DEFAULT ('Rasmussen')
ALTER TABLE People
DROP CONSTRAINT People_Lastname_Temp_Constraint
By running the above statements we get all the old rows in the table set to the value "Rasmussen", but going forward you have to provide a value for the LastName column.
I hope you found this helpful and let you run your alter statement, please leave a comment down below!
This is a 2022 rewrite of my previous post on this from 2020.