Cannot modify table ( using microsoft sql server management studio 2008 )

Question

I create 2 tables and another 1 with foreign keys to the other two.

I realized I want to make some changes to table no 3.

I try to update a field but I get an error "Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created."

I delete those 2 relationships but when I look at dependencies I see my table still depends on those 2 and I still cannot make any change to it.

What can I do?

Solution 1

You can also enable saving changes that require dropping of tables by going to "tools->options->designers->Table and database designers" and unchecking "Prevent saving changes that require table re-creation"

Be careful with this though, sometimes it'll drop a table without being able to recreate it, which makes you lose all data that was in the table.

Solution 2

When using Microsoft SQL Server Management Studio 2012, the same message occurs. I used the script feature to do modifications which can be seen as a rather good workaround if you wanna use the designer only within a "safe" mode. Especially the GUI related to create a foreign key is not the best in my opinion. When using a script (alter table) for adding a fk, you are faster than using this GUI feature. When adding/writing a 'not' in prior to null, that's not a hard issue. (Removing an 'Allow Nulls' for a column refers to "Saving changes is not permitted" when using the designer.)