Saving changes is not permitted. The changes that you have made require the following tables to be d

最新推荐文章于 2024-11-24 20:48:29 发布

欢乐的小猪

最新推荐文章于 2024-11-24 20:48:29 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： it is nothing 文章标签： sql server table ssms database validation structure

本文链接：https://blog.youkuaiyun.com/hbu_pig/article/details/7574861

it is nothing 专栏收录该内容

15 篇文章

订阅专栏

当尝试在SQL Server 2008 Management Studio（SSMS）中修改表结构并遇到'Saving changes is not permitted'错误时，可以禁用'防止保存要求重新创建表的变化'选项来解决。该错误发生是因为结构更改需要删除并重新创建表。禁用此选项后，可以进行包括插入列、更改列属性等在内的表结构更改，但需要注意，如果启用了变更跟踪，重新创建表会导致变更跟踪信息丢失。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

ERROR:

“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.”

This error happens because “Saving Changes is not permitted” when using SQL Server 2008 Management Studio to modify the structure of a table especially when SQL Server needs to drop and recreate a table to save the structural changes. It is always advised to make a structural change for a table using TSQL. However, it is a convenient option for database developers to use SQL Server Management Studio make such changes as the Prevent Saving Changes That Require Table Re-creation option is enabled by default in SQL Server 2008 Management Studio.

Disable “Prevent saving changes that require the table re-creation”

1. Open SQL Server 2008 Management Studio (SSMS). Click Tools menu and then click on Options… as shown in the snippet below.

2. In the navigation pane of the Options window, expand Designers node and select Table and Database Designers option as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes.

This option when enabled prevents users from making structural changes to table using SQL Server Management Studio especially when SQL Server needs to recreate the table to save changes. By default, this option is checked and you need to uncheck this option to allow users to make any structural change through SSMS that require table recreation.

A table needs to be recreated whenever any of the below changes are made to the table structure.

· Insert a new column in the middle of the table.

· Add a new column in the table.

· Change the Allow Nulls setting of a column.

· Modify the identity property of a column.

· Reorder the columns within a table.

· Modify the datatype of a column.

Once you have disabled “Prevent saving changes that require the table re-creation” option you can go ahead and save the changes to the Employee Table. This will create a Validation Warning dialog box as shown in the below snippet. The warning message will be “One or more existing columns have ANSI_PADDING ‘off’ and will be re-created with ANSI_PADDING ‘on’”. Click “Yes” to save the changes.

Risks of Turing Off “Prevent Saving Changes that Require Table Re-creation” in SSMS

If you turn off this feature then you can avoid table recreation. However, if you have the Change Tracking feature of SQL Server 2008 enabled to track the table changes then the change tracking information will be lost completely whenever table is recreated. So, it is always advised to use this feature very carefully especially in a production environment.