Programming Articles and Tips

Follow by Email

Monday, July 8, 2013

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.