Problem
Recently someone asked a question: Why is it unable to save changes after editing a table design in SQL Server Management Studio? This is an interesting question. Usually we will not alter the table form the SSMS design screen. In case if you try editing the table which is having data and primary and foreign key relations, and save the changes, you may get the below message.
Saving changes is not permitted. The changes 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.
Solution
This is because, by default, SSMS prevents saving changes that require table re-creation. You can un-select this setting in the options screen. Go to menu Tools >> Options >> Designers and un-select the option Prevent saving changes that require table re-creation and press OK to save the settings. Now, if you edit a table design and try to save, you may get warnings based on the table’s relationship with other tables and its complexities. You can press Yes to override the warnings to save your changes.
Below is the warning I got when I tried to save a change in the design of the table Sales.Orders in the sample database WorldWideImporters.
Conclusion
As you have read above, there is an option to override the default settings in SSMS which prevents saving the changes in table design. However, it is not recommended. Instead of using SSMS table design window, whenever you want to alter a table, do an impact analysis and create alter scripts for the impacted tables as well. Also, in rare cases, you may need to create migration scripts to save the data to temp tables and back to the original tables to avoid data loss.