Error: You cannot save changes that would result in one or more tables being re-created

When trying to save something to a table, such as new fields or isNull tick box, set primary keys or change data types, the above error may pop up. This may happen due to attempts to:

  • Change data type on existing columns such as text to varchar
  • Check allow nulls or uncheck allow nulls on existing columns
  • Change column sizes
  • Change foreign key constraints

The solution is easy, if you already have the SQL Management Studios,

Using Visual Studio 2008:

  • click Tools, select Options
  • from the pop up, select Designers from the left side menu
  • Uncheck the “Prevent saving changes that require table re-creation” as shown below
  • Save

Using Visual Studio 2010 or Visual Studio 2012:

  • click Tools, select Options
  • from the pop up, select “Database Tools” from the left side menu
  • from the sub-categories of Database Tools, select “Table and Database Designers”
  • Uncheck the “Prevent saving changes that require table re-creation”
  • Save

vs2012

 

If you are using the built in SQL server express in-built in the Visual Studio 2008 or 2010, you are required to download the SQL management studio 2008 and possibly also the SQL Server 2008 Service Pack 1 (try without installing Service Pack 1 first)

Available from:

Sql Management Studio 2008: http://www.microsoft.com/download/en/details.aspx?id=7593

Sql Service Pack 1: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20302