If you’re getting an error in SQL Server that reads something like “ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or… etc etc“, it’s probably because you’re trying to add a NOT NULL column to a table that already contains data.
This error occurs due to the fact that the NOT NULL constraint will be violated for every row in the table. Think about it for a second. When you first add the column, there’s no data. It’s not until you run a subsequent INSERT statement (or some other process that populates the table) that you will get data. In the meantime, all values in your new column will be NULL. And that, of course, violates the NOT NULL constraint.
Fortunately, there are various options to fix this issue, as outlined in the error message.
Example of Error
Here’s an example of code that produces the error:
ALTER TABLE Users
ADD IsActive BIT NOT NULL;Output:
Msg 4901, Level 16, State 1, Line 2
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'IsActive' cannot be added to non-empty table 'Users' because it does not satisfy these conditions.
This error occurs because we’re trying to specify the new column as NOT NULL on a table that already contains data. The problem is that we can’t add data into the new column until later (i.e. with a different statement). If we hadn’t specified NOT NULL, the column would’ve been created with NULL values in all rows. We could then go and insert data into all rows.
But our NOT NULL constraint brings that idea to a screeching halt. By setting the column to NOT NULL, we’re specifying that the column cannot be NULL – not even in the short period between adding the column and inserting its data. Therefore the table can’t be created. Not unless we set a default value or specify the column as an IDENTITY or TIMESTAMP column.
Solution
As mentioned, we will need to do one of the following if we want our ALTER TABLE statement to succeed:
- Set a default value
- Make it an identity or timestamp column
In our example, we’re using a bit column, so let’s set a default value:
ALTER TABLE Users
ADD IsActive BIT NOT NULL DEFAULT 1;Output:
Commands completed successfully.
No error this time. That statement set all rows to have a default value of 1 in the IsActive column.
As alluded to, it’s possible to omit the default value if the new column is either an IDENTITY or TIMESTAMP column. For example:
ALTER TABLE Users
ADD UserID INT IDENTITY;That will work without error. Bear in mind that you won’t be able to create an IDENTITY column if the table already has one (only one identity column can be created per table in SQL Server). Also, once the identity property is set on a column, it can’t be removed (although it can be changed to another data type, as long as the new type is compatible with the IDENTITY property).