SQL Server Mgmt Studio messing up my Database!

Question

This has effectively ruined my day. I have a larger number of tables with many FK relationships in between. One of the tables (lets call it table A) has a computed column, which is computed via a UDF with schemabinding and is also fulltext indexed.

If I edit any table (lets call it table B) that in any way is related (e.g via FK) to the table with the fulltext indexed computed column (table A), and I save it, the following happens:

• Changes to the table (table B) are saved
• I get the error: "Column 'abcd' is no fulltext indexed." regarding table A which I didn't even edit, and then "User canceled out of save dialog"
• All FK relationships to ALL TABLES from Table B are DELETED

What the hell is going on??? Can someone explain to me how this can happen?

Answer 1

I've had the same kind of problem. As Will A said, the management studio will do the following steps to update a table and its foreign keys:

1. Create a new table called temp_
2. Copy contents from old table into new
3. Drop all constraints, indexes and foreign keys
4. Drop old table
5. Rename new table to be = old table
6. Recreate the foreign keys, indexes and constraints

I may have the first 3 in the wrong order but you get the idea. In my case I've lost entire tables not just the foreign keys. Personally I don't like the way it does it as it can be VERY time consuming to have to recreate indexes on a table with lots of data in. If its a small change I usually do it myself in T-SQL.

Review the change script before it executes it, make sure it looks sensible.

@OMGPonies, why can't you drop a foreign key if there is data in the table? Of course you can. There are only restrictions on creating foreign keys on tables with data but that is only if it breaks the constraint. However even that can be avoided by using the WITH NOCHECK option when creating the key. Yes I know it'll break when you try to update a broken result set.