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.
This question is related to
sql
sql-server
sql-server-2008
Prevent saving changes that require table re-creation
Five swift clicks
- Tools
- Options
- Designers
- Prevent saving changes that require table re-creation
- 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.
Just go to the SQL Server Management Studio -> Tools -> Options -> Designer; and Uncheck the option "prevent saving changes that require table re-creation".
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.
Source: Stackoverflow.com