Error Message:
Msg 4901, Level 16, State 1, Line 1
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 ‘%.*ls’ cannot be added to non-empty table ‘%.*ls’ because it does not satisfy these conditions.
Severity level:
16.
Description:
This error message appears when you try to add a column for which no DEFAULT constraint has been specified with the NOT NULL constraint to an existing table.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must either remove the NOT NULL constraint when adding the column to the table, or specify a DEFAULT constraint for the column in the ALTER TABLE statement.
Versions:
All versions of SQL Server.
Example(s):
USE tempdb;
GO
IF OBJECT_ID(‘tempdb..#t’) > 0
DROP TABLE #t
GO
CREATE TABLE #t
(
id INT
)
GO
INSERT INTO #t SELECT 1
GO
ALTER TABLE #t ADD c2 INT NOT NULL
Remarks:
In the above example we try to add the column c2 to the table #t. c2 should be added with a NOT NULL constraint, but without a DEFAULT constraint. Because there is already data in the table #t, the error is raised.