无法更改 SQL Server 2008 中的表设计

Can't change table design in SQL Server 2008

提问by tharindlaksh

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

我创建了一个 table tbl_Candidate，但是我忘记设置 table 的主键并且我没有主键保存它。

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.

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

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

不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的表进行了更改，或者启用了阻止保存需要重新创建表的更改的选项

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

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

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

请给我一种纠正此问题的方法或更改 SQL Server 设置的方法。

回答by pylover

The answer is on the MSDN site:

答案在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:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

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

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

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

EDIT 1:

编辑 1：

Additional useful informations from here:

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

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

1. Open SQL Server Management Studio (SSMS).
2. On the Tools menu, click Options.
3. In the navigation pane of the Options window, click Designers.
4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

NoteIf 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.

Risk of turning off the "Prevent saving changes that require table re-creation" option

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.

要更改阻止保存需要重新创建表的更改选项，请执行以下步骤：

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

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

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

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

回答by Kishore Kumar

You can directly add a constraint for table

您可以直接为表添加约束

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName PRIMARY KEY(ColumnName)
GO 

Make sure your primary key column should not have any null values.

确保您的主键列不应包含任何空值。

Option 2:

选项 2：

you can change your SQL Management Studio Options like

您可以更改您的 SQL Management Studio 选项，例如

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

若要更改此选项，请在“工具”菜单上，单击“选项”，展开“设计器”，然后单击“表和数据库设计器”。选中或清除阻止保存需要重新创建表的更改复选框。

回答by WonderWorker

Prevent saving changes that require table re-creation

Five swift clicks

1. Tools
2. Options
3. Designers
4. Prevent saving changes that require table re-creation
5. OK.

After saving, repeat the proceudure to re-tick the box. This safe-guards against accidental data loss.

防止保存需要重新创建表的更改

五次快速点击

1. 工具
2. 选项
3. 设计师
4. 防止保存需要重新创建表的更改
5. 好的。

保存后，重复该程序以重新勾选该框。这可以防止意外数据丢失。

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.

• 默认情况下，SQL Server Management Studio 会阻止删除表，因为删除表时其数据内容将丢失。*

• 在表设计视图中更改列的数据类型时，在保存更改时，数据库会在内部删除该表，然后重新创建一个新表。

*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.

*您的具体情况不会造成任何后果，因为您的桌子是空的。我提供此解释完全是为了提高您对程序的理解。

回答by Sanket Gadade

Just go to the SQL Server Management Studio -> Tools -> Options -> Designer; and Uncheck the option "prevent saving changes that require table re-creation".

只需转到 SQL Server Management Studio -> 工具 -> 选项 -> 设计器；并取消选中“防止保存需要重新创建表的更改”选项。