If you modify structure of database table which requires the table to be recreated and try to save it in SQL Server 2008, you may receive an error message. For example, I modify Categories table in Northwind database by check AllowNulls on CategoryName column as the figure below.
Then, I try to save changes of the table, I will see the error message as following:
Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.
And when you click cancel, you will see this message dialog.
User canceled out of save dialog
This error message occurs because of the default option on SQL Mangement Studio which prevents you to save changes on a table that require the table re-creation. So why prevent this? Because there may be a chance of data loss when you save changes on a table if the table need to be re-created. You can read about this by follow the link on reference section at the end of this post.
Microsoft recommends you to use Transact-SQL statements to make the changes of a table. But if you don’t want to write such statements, or you are working on test environment, or you do have a backup of database, you can simply turn off this option by follow instruction below.
Step-by-step to solve “Saving changes is not permitted”
- On Microsoft SQL Server Management Studio, select Tools -> Options.
- On Options, expand Designers and select Table and Database Designers. Then, uncheck the Prevent saving changes that require the table re-creation check box and click OK.
- Now you can save changes on the table.
I wonder who it is at Microsoft who turns up to the meetings and says ‘Out of the box absolutely nothing should work, people love seeing error messages instead of results and enjoy tracking down solutions to problems that shouldn’t exist, they will thank us for making software that doesn’t allow them to hurt themselves’. Damn I want this fools blood.
Yeah I found this annoying. Why make it difficult?
I could see turning off this setting on production servers, but not on everything.
http://chrisbarba.com/2009/04/15/sql-server-2008-cant-save-changes-to-tables/
i tried but it’s not OK
‘Subjects’ table saved successfully
‘Student’ table saved successfully
‘Marks’ table
– Unable to create relationship ‘FK_Marks_Subjects1’.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_Marks_Subjects1”. The conflict occurred in database “Demo2016”, table “dbo.Subjects”, column ‘Id’.
vương xuân sơn you have some records related to that table which is not allowing you to delete it
thank you for this solution