Sql Server“不允许保存更改”错误►防止保存需要重新创建表的更改
当我在 SQL Server 中创建表并保存它时，如果我尝试编辑表设计（例如将列类型从 int 更改为 real），则会收到以下错误：
不允许保存更改。您所做的更改需要删除并重新创建下表。您对无法重新创建的表进行了更改，或者启用了阻止保存需要重新创建表的更改的选项。
为什么我必须重新创建表？我只想将数据类型从 smallint 更改为 real。
桌子是空的，我到现在都没用过。
When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:
Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.
Why do I have to re-create the table? I just want to change a data type from smallint to real.
The table is empty, and I didn't use it until now.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（15）
来自 MSDN 上的“保存（不允许）”对话框：
参见
Colt Kwong 博客条目：
SQL 2008 Management Studio 中不允许保存更改
From Save (Not Permitted) Dialog Box on MSDN :
See Also
Colt Kwong Blog Entry:
Saving changes is not permitted in SQL 2008 Management Studio
如果您使用 SSMS：
转到菜单工具>>选项>>设计器并取消选中阻止保存需要重新创建表的更改
If you are using SSMS:
Go to the menu Tools >> Options >> Designers and uncheck Prevent Saving changes that require table re-creation
进一步说明
默认情况下，SQL Server Management Studio 会阻止删除表，因为当删除表时，其数据内容就会丢失。*
在表设计视图中更改列的数据类型时，保存更改时，数据库会在内部删除表，然后重新创建一个新表。< /p>
*您的具体情况不会造成任何后果，因为您的桌子是空的。我提供此解释完全是为了提高您对该过程的理解。
Further explanation
By default SQL Server Management Studio prevents the dropping of tables, because when a table is dropped its data contents are lost.*
When altering a column's datatype in the table Design view, when saving the changes the database drops the table internally and then re-creates a new one.
*Your specific circumstances will not pose a consequence since your table is empty. I provide this explanation entirely to improve your understanding of the procedure.
这可以在 Microsoft SQL Server 中轻松更改。
This can be changed easily in Microsoft SQL Server.
要更改阻止保存需要重新创建表的更改选项，请按照下列步骤操作：
打开 SQL Server Management Studio (SSMS)。
在“工具”菜单上，单击“选项”。
在“选项”窗口的导航窗格中，单击“设计器”。
选中或清除“防止保存需要重新创建表的更改”复选框，然后单击“确定”。
注意：如果禁用此选项，则在保存表时不会警告您所做的更改已更改表的元数据结构。在这种情况下，保存表时可能会发生数据丢失。
To change the Prevent saving changes that require the table re-creation option, follow these steps:
Open SQL Server Management Studio (SSMS).
On the Tools menu, click Options.
In the navigation pane of the Options window, click Designers.
Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.
Note: If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.
这是非常简单的设置问题，按照以下步骤可以在 5 秒内修复
为了允许您在更改表后保存更改，请按照以下步骤进行 SQL 设置：
It is very easy and simple setting problem that can be fixed in 5 seconds by following these steps
To allow you to save changes after you alter table, Please follow these steps for your sql setting:
进入顶部菜单的工具。
从下拉列表中选择选项。现在，您会在左侧菜单块上弹出一个选择设计器选项的窗口。取消选中防止保存需要重新创建表的更改选项。单击“确定”按钮。
Go on Tool located at top menu.
Choose options from dropdown.You have a popup now select Designers option located on left hand block of menus. Uncheck the option Prevent saving changes that require table re-creation. Click on OK Button.
在工具►选项►设计器选项卡中取消勾选
防止保存需要重新创建表的更改框。SQL Server 2012 示例：
Un-tick the
Prevent saving changes that require table re-creationbox from Tools ► Options ► Designers tab.SQL Server 2012 example:
复制自此链接
... 重要提示 我们强烈建议您不要通过关闭“防止保存需要重新创建表的更改”选项来解决此问题。有关关闭此选项的风险的更多信息，请参阅“更多信息”部分''
" ...要解决此问题，请使用 Transact-SQL 语句更改表的元数据结构。有关其他信息，请参阅 SQL Server 联机丛书中的以下主题
例如，要更改名为 MyTable 的表中 datetime 类型的 MyDate 列以接受 NULL 值，您可以使用：
alter table MyTable alter columns MyDate7 datetime NULL "
Copied from this link
" ... Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section. ''
" ...To work around this problem, use Transact-SQL statements to make the changes to the metadata structure of a table. For additional information refer to the following topic in SQL Server Books Online
For example, to change MyDate column of type datetime in at table called MyTable to accept NULL values you can use:
alter table MyTable alter column MyDate7 datetime NULL "
以防万一这里有人也没有注意（像我一样）：
对于 Microsoft SQL Server 2012，在选项对话框中，有一个偷偷摸摸的小复选框，它显然隐藏了所有其他设置。虽然我不得不说我一直很想念那个小怪物！
之后，您可以继续执行步骤，设计者，取消选中防止保存等等等等...
And just in case someone here is also not paying attention (like me):
For Microsoft SQL Server 2012, in the options dialogue box, there is a sneaky little check box that APPARENTLY hides all other setting. Although I got to say that I have missed that little monster all this time!!!
After that, you may proceed with the steps, designer, uncheck prevent saving blah blah blah...
1）打开顶部的工具。
2) 从选项列表中选择选项。
3）现在出现弹出窗口，您现在可以从左侧的菜单列表中选择设计器选项。
4) 现在需要取消选中需要重新创建表的保存更改。 现在点击“确定”。
1) Open tool which is on top.
2) Choose options from Picklist.
3) Now Comes the popup and you can now select designers option from the list of menus on the left side.
4) Now prevent saving changes need to be unchecked that needed table re-creation. Now Click OK.
从“工具”菜单中，单击“选项”，从侧面菜单中选择“设计器”，然后取消选中“防止可能导致重新创建表的更改”。然后保存更改
From the Tools menu, click on Options, select Designers from the side menu and untick prevent changes that can lead to recreation of a table. Then save the changes
如果您使用 sql server Management studio，请转到
工具>>选项>>设计者并取消选中“防止保存需要重新创建表的更改”
它对我有用
If you use sql server Management studio go to
Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation”
It works with me
如果您在列表中看不到“防止保存需要重新创建表的更改”
图片
您需要启用更改跟踪。
If you can not see the "Prevent saving changes that required table re-creation" in list like that
The image
You need to enable change tracking.
实际上，您被 SSMS 阻止，而不是 SQL Server。
解决方案是更改 SSMS 的设置或使用 SQL 查询。
使用 SQL 查询您可以自由地进行更新。
例如，您想向表中添加新列，您可以这样做：
其他选项是更改 SSMS 设置。请参考其他答案，很多人已经解释过了。
Actually, You are blocked by SSMS not the SQL Server.
The solution are either change setting of SSMS or use a SQL query.
Using SQL Query you could do the update freely.
Example you want to add a new column to a table, you could do like this :
Other option is changing SSMS setting. Please refer to other answer, as many has explain it.