Click here to Skip to main content
15,922,315 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
hi i added a column in my table. but i added it as null. i want to change it as not null.when i change using this query

alter table tbl_user_login alter column created_date datetime not null


it shows an error
Cannot insert the value NULL into column 'created_date', table 'customer_habitat_afsal.dbo.tbl_user_login'; column does not allow nulls. UPDATE fails.



what can i do?
help me..
thanks in advance
Posted
Updated 25-Nov-11 22:44pm
v2

Firstly fill the null values with an arbitrary data for example current date time then alter that column :

SQL
update tbl_user_login set created_date= getdate() where created_dateis null ;


alter table tbl_user_login alter column created_date datetime not null


Hope it helps.
 
Share this answer
 
Comments
RaviRanjanKr 27-Nov-11 14:34pm    
My 5+
SQL Server is checking the values in the table so they do not violate the not null and they do hence the error.

You can insert values into the null columns and rerun the query.
 
Share this answer
 
first enter current date or anyother date in this field as you want than alter the column. then it will work. because in your field there is null value stored so it is giving error.

SQL
update tbl_user_login set created_date=@Date where created_date is null
alter table tbl_user_login alter column created_date datetime not null
 
Share this answer
 
Do you really need to programmaticaly set this one? Just edit the field in design view.

Regards,
Eduard
 
Share this answer
 
here is tutorial to alter a table...

http://www.gigasters.com/post.aspx?postid=18
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900