[Resolved] ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified..Column '' cannot be added to non-empty table '' because it does not satisfy these conditions. | Msg 4901, Level 16, State 1 | SQL alter table error

Hi in this post i will show how to resolve this below error.

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 '' cannot be added to non-empty table '' because it does not satisfy these conditions.

1. Creating a scenario where you can get this type of issues.

create table empDetails

(

nameid int identity primary key,

name varchar(1000),

addres varchar(1000),

mobile numeric

)

insert into empDetails(name,addres,mobile) values ('chandan','india',123456789)

select * from empdetails

2. Now i will add a column to this table  

alter table empdetails

add [deptid] [int] NOT NULL 

Executing this wil give me the error as

To resolve this we will add default value '0' to the column.

Thus the error gets resolved.