Smart rename, last step disable constraints?

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

Smart rename, last step disable constraints?

Postby wdhenrik » Thu Nov 01, 2012 11:22 pm

I'm using smart rename to rename a table and I noticed the very last action taken before 'The database updated succeeded' is to Disable the constraints on the newly named table.
Code: Select all
PRINT N'Disabling constraints on [dbo].[NewTableName]'
GO
ALTER TABLE [dbo].[NewTableName] NOCHECK CONSTRAINT [FK_NewTableName_ExternalTable]

Is this intentional? I'm not sure if I'm misunderstanding something, but why would I want to disable the constraints after the table (with data) has been renamed and all of the constraints have been recreated.

Shouldn't this be
Code: Select all
ALTER TABLE [dbo].[NewTableName] CHECK CONSTRAINT [FK_NewTableName_ExternalTable]
instead?

My preference would be to validate existing data as well,
Code: Select all
ALTER TABLE [dbo].[NewTableName] WITH CHECK CHECK CONSTRAINT [FK_NewTableName_ExternalTable]

but I understand that cannot work as a default.
wdhenrik
 
Posts: 12
Joined: Thu Jul 19, 2012 11:58 pm

Postby Brian Donahue » Mon Nov 05, 2012 4:41 pm

Hello,

I am having trouble replicating this issue. In every case I can find, SQL Prompt 5.3 drops and recreates the foreign key rather than disabling it. Or is this actually a check constraint with "FK" in the name?

If you can script up a quick reproduction of this particular environment and email it to support@red-gate.com, I'd be happy to have a look.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby wdhenrik » Tue Nov 06, 2012 4:12 pm

Thank you again Red Gate for inadvertantly revealing a problem with a database. :D

While compiling some scripts to send you, I noticed that the constraint is scripted as disabled by SSMS as well. Apparently, someone disabled this constraint at some time in the past and never enabled it again. :x

I doubt I would have ever noticed this if not for your scripting tools summary, where it finished with
Code: Select all
PRINT N'Disabling constraints on ...'
.

Problem solved.

Thanks again,
Wes
wdhenrik
 
Posts: 12
Joined: Thu Jul 19, 2012 11:58 pm

Postby Brian Donahue » Wed Nov 07, 2012 5:09 pm

Hello Wes,

Thanks for following up. I'll deactivate the support issue.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests