无法更改 SQL Server 2008 中的表设计 [英] Can't change table design in SQL Server 2008

问题描述

我创建了一个表tbl_Candidate，但是我忘记给表设置主键，我保存时没有主键.

I created a table tbl_Candidate, but I forgot to set the primary key to the table and I saved it without primary key.

下次我要在 SQL Server 2008 Express 中设置主键时，我收到一条消息，比如当我要保存主键更改的表时，我必须删除表并重新创建.

Next time I'm going to set primary key in SQL Server 2008 Express, I get a message like I have to drop the table and recreate when I'm going to save the table with primary key changes.

这是当我尝试保存表格中的更改时弹出的消息:

This is the message that pops up when I'm trying to save changes in the table:

不允许保存更改.您所做的更改需要删除并重新创建以下表.你要么做了对无法重新创建或启用该选项的表的更改防止保存需要重新创建表的更改

Saving changes is not permitted. The changes you have made require that the following tables to dropped and recreate. You have either made changes to the table that can't be recreate or enable the option prevent saving changes that require the tables to be recreate

我使用 Windows 身份验证登录 SQL Server，服务器类型为数据库引擎，服务器名称为 .\SQLExpress.

I'm logged into SQL Server with Windows authentication, server type as database engine and Server name as .\SQLExpress.

请给我一个纠正这个问题的方法或更改 SQL Server 设置的方法.

Please give me a way to correct this problem or way to change the settings of the SQL Server.

推荐答案

答案在 MSDN 站点:

保存(不允许)对话框警告您保存更改是不允许，因为您所做的更改需要列出的要删除和重新创建的表.

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

以下操作可能需要重新创建表:

The following actions might require a table to be re-created:

• 在表格中间添加一个新列
• 删除一列
• 更改列的可空性
• 改变列的顺序
• 更改列的数据类型

编辑 1:

来自此处的其他有用信息:

更改阻止保存需要表的更改重新创建选项，请按照下列步骤操作:

To change the Prevent saving changes that require the table re-creation option, follow these steps:

1. 打开 SQL Server Management Studio (SSMS).
2. 在工具"菜单上，单击选项".
3. 在选项"窗口的导航窗格中，单击设计器.
4. 选中或清除阻止保存需要重新创建表的更改复选框，然后单击确定.

注意如果禁用此选项，则在保存表时不会警告您所做的更改已更改元数据表的结构.在这种情况下，您可能会丢失数据保存表格.

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.

关闭防止保存需要重新创建表的更改"选项的风险

虽然关闭此选项可以帮助您避免重新创建表，它也可能导致更改丢失.例如，假设启用 SQL Server 2008 中的更改跟踪功能以跟踪对表的更改.当您执行导致要重新创建的表，您会收到错误消息在症状"部分中提到.但是，如果您关闭此功能选项，现有的更改跟踪信息在表被重新创建.因此，我们建议您不要工作通过关闭该选项来解决这个问题.

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the "Symptoms" section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

这篇关于无法更改 SQL Server 2008 中的表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！