1
Vote

Disabled foreign key constraints not generated properly by Wizard in output scripts

description

T-SQL scripts generated by the SQL Azure Migration Wizard v3.7.7 do not generate ALTER TABLE ... NOCHECK statements for disabled foreign key constraints at the right point in the output script. The ALTER TABLE ... NOCHECK statements are generated in the script before the foreign keys they reference are actually created in the script.
 
For example, the NOCHECK statement for foreign key FK_MYFK1 on table MyTable1 is generated on line 559 of the output T-SQL migration script:
 
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MyFK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[MyTable1]'))
ALTER TABLE [dbo].[MyTable1] NOCHECK CONSTRAINT [FK_MyFK1]
 
However, the statement to actually create FK_MYFK1 on MyTable1 is not generated until line 13370 of the same script:
 
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MyFK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[MyTable1]'))
ALTER TABLE [dbo].[MyTable1] WITH NOCHECK ADD CONSTRAINT [FK_MyFK1] FOREIGN KEY([EntityID])
REFERENCES [dbo].[MyTable2] ([MyTable2ID])
 
The names of the actual tables have been changed to protect the innocent, but the line numbers indicating the positioning issue are from a real instance of running the migration wizard recently with v3.7.7.

comments

ghuey wrote Sep 20, 2011 at 1:49 PM

Hi, but I am a little slow this morning. What kind of error is this causing you? I believe that I had trapped all alter table commands and moved them down because of tables with circular references was causing a big problem. If you want, you can contact me offline and we can discuss. Just ping me through codeplex.

Thanks,
George