Error: 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 ” cannot be added to non-empty table ” because it does not satisfy these conditions.
Description: Try below statement on an existing table which contains records.
ALTER TABLE table1 ADD col1 BIT NOT NULL
Above error will be shown, because SQL statement not saying what value should be added or updated for existing records. When we say NOT NULL, we must provide a value for the existing fields.
Solution: Add a DEFAULT value or “Allow Null”.
ALTER TABLE table1 ADD col1 BIT DEFAULT 0 NOT NULL ALTER TABLE table1 ADD col1 BIT NULL
#stopdebugging 