Few days ago I have migrated one of my Database from SQL Server 2005 to SQL Server 2008. Actually what I did during the migration? I have created a Database first in SQL Server 2008. Then I have imported all of my SQL Server 2005 tables into the SQL Server 2008 Database. I thought that Everything is perfect but when I start one of my application on this database then I found that System can not insert data into few tables. I have started debugging my application and found that my imported tables has missed identity constraint. So what can I do? According to SQL Server 2005, my understanding was I can apply identity constraint on numeric columns if the column has a sequence of unique number. So I open my table (SQL Server 2008 version) in design mode then click on ID column then from Column Properties select identity = yes and then save. But at that time I found the below error:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.
The snapshot is given below:
The error is self explanatory and helpful enough to find a solution clearly.
After few minutes i found the below solution:
GO TO Tools >> Options >> Designers
Then you will found the option “Prevent saving changes that require table re-creation” is checked. Just uncheck the option will resolve your problem.
Please find the step by step screenshots from below:
GO TO Tools >> Options
Now just uncheck the option will resolve your problem:
Hope it will help you.