19

I've got a SQL Database Table, which has 35 existing records. One of the fields in this table is called Name, nvarchar(100), not null

However, due to a recent change, I need to make this column nullable.

When I change the column to allow nulls in SQL Server Management Studio, and go to save my changes, I get the following error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created

How can I allow this to automatically be dropped and re-created?

Curtis
  • 101,612
  • 66
  • 270
  • 352

3 Answers3

39

I've found the solution. Go to "Tools > Options > Designers > Table and Database Designers":

enter image description here

Curtis
  • 101,612
  • 66
  • 270
  • 352
  • 1
    The only thing that's a waste of people's time is marking something as an answer that doesn't actually help someone with the same problem. ;-) – Oran Dennison Oct 22 '12 at 19:29
  • @Oran Haha, my guess is you've not got the same problem, or else I wouldnt of marked it as answer. Perhaps a different SQL Server version?.. – Curtis Oct 22 '12 at 20:03
  • MS says that re-creation of the table can result in data loss and warns about turning of this option. – Maxence Jun 20 '14 at 09:51
  • @Maxence's warning is well taken. I turned off this safety option, made my changes, then turned it back on to keep me from accidentally losing data later. – Tim Sep 09 '14 at 22:31
6

It's a setting in SSMS.

Tools - Option - Designers - Prevent saving changes that require table re-creation

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
3

I had the same problem; wanting to Allow Nulls for a column that previously did not. Consider MS's warning to NOT turn off this option: http://support.microsoft.com/kb/956176

And their recommendation to use Transact-SQL to solve the problem, e.g. alter table MyTable alter column MyDate7 datetime NULL

This solved it for me.

David Carr
  • 400
  • 4
  • 7