How can we help you today? How can we help you today?

Smart rename, last step disable constraints?

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. 
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 
ALTER TABLE [dbo].[NewTableName] CHECK CONSTRAINT [FK_NewTableName_ExternalTable]
instead?

My preference would be to validate existing data as well, 
ALTER TABLE [dbo].[NewTableName] WITH CHECK CHECK CONSTRAINT [FK_NewTableName_ExternalTable]
but I understand that cannot work as a default.
wdhenrik
0

Comments

3 comments

Sort by
  • Brian Donahue
    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
    0
  • wdhenrik
    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 
    PRINT N'Disabling constraints on ...'
    .

    Problem solved.

    Thanks again,
    Wes
    wdhenrik
    0
  • Brian Donahue
    Hello Wes,

    Thanks for following up. I'll deactivate the support issue.
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.