Smart rename, last step disable constraints?

wdhenrik

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.

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.

wdhenrik

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

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

Brian Donahue

Hello Wes,

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