What are the challenges you face when working across database platforms? Take the survey

Smart rename, last step disable constraints?

wdhenrikwdhenrik Posts: 15 Bronze 1
edited November 7, 2012 11:09AM in SQL Prompt Previous Versions
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]'
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]

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.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    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.
  • Options
    wdhenrikwdhenrik Posts: 15 Bronze 1
    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,
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Wes,

    Thanks for following up. I'll deactivate the support issue.
Sign In or Register to comment.