Can't change table design in SQL Server 2008

Question

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

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

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

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

Answer 1

The answer is on the MSDN site:

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

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

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.

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.

Answer 2

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:

you can change your SQL Management Studio Options like

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.

Answer 3

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.

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.

Answer 4

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