Creating foreign key if exists.

I have the following SMO code:

// Create options

ScriptingOptions so = new ScriptingOptions();

so.IncludeIfNotExists = true;

so.DriAll = true;

// For scripting check constraints

so.DriChecks = true;

// For scripting extended properties

so.ExtendedProperties = true;

// Don't allow system objects

so.AllowSystemObjects = false;

StringCollection individualForeignKeyCreation = foreignKey.Script(so);

Notice that the scripting option is set to include if the table exists. But the script that is generated is like:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReasonCodeSubCategory_ReasonCodeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReasonCodeSubCategory]'))
ALTER TABLE [dbo].[ReasonCodeSubCategory] WITH CHECK ADD CONSTRAINT [FK_ReasonCodeSubCategory_ReasonCodeCategory] FOREIGN KEY([ReasonCodeCategoryID])
REFERENCES [ReasonCodeCategory] ([ReasonCodeCategoryID])
ALTER TABLE [dbo].[ReasonCodeSubCategory] CHECK CONSTRAINT [FK_ReasonCodeSubCategory_ReasonCodeCategory]

If "if" condition takes care of the first line but it seems that there should be a BEGIN END so the "if" condition holds for both ALTER statements. The reason this came up is when I try to run this script I get an error. I could modify the string collection but even this has been made difficult because it seems the 'if" and the first ALTER statement are one string in the collection returned from Script.

Any suggestions?

Thank you.

Kevin

Kevin,

Thank you for reporting this issue. This is a defect in SMO. I opened a bug report and we will fix it in the next realease (the fix will likely appear in one of the upcoming CTP releases)