SQL Server Data Compare not persisting "ON DELETE SET NULL" Foreign Key Relationship

Question

I have a feeling this is an "only on my machine" problem. I have two SQL 2008 R2 Express databases on my box. The schema for each was created with the same scripts. The data differs.

When I use EITHER Visual Studio 2010's built-in data comparison tool OR Red Gate's SQL Data Compare v7 tool, I see the following behavior.

• A foreign key with ON DELETE SET NULL is dropped
• Data is inserted
• The foreign key is recreated without the ON DELETE SET NULL option.

Looking at the SQL Scripts generated by either tool clearly shows the problem. As far as I can tell, the rest of my FK's are fine, many of which use ON DELETE CASCADE.

When I ask SSMS to generate a script for the FK itself it includes the ON DELETE SET NULL specifier, so it's not my imagination.

How could two distinct tools fail to regenerate the correct SQL for this key? Is there a misconfiguration of SQL Server, or a limitation on the express edition?

Answer 1

I've recently found a new tool Optillect Data Compare SQL that works better than others. May be you should try it..

Answer 2

If you're dropping the FKs in order to not have to worry about data integrity during your load, how about disabling them instead? That way, the definition stays in the database.