Red Gate forums :: View topic - Smart rename, last step disable constraints?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Prompt 5
SQL Prompt 5 forum

Smart rename, last step disable constraints?

 Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
wdhenrik



Joined: 19 Jul 2012
Posts: 5
PostPosted: Thu Nov 01, 2012 11:22 pm    Post subject: Smart rename, last step disable constraints? Reply with quote
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:
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:
ALTER TABLE [dbo].[NewTableName] CHECK CONSTRAINT [FK_NewTableName_ExternalTable]
instead?

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

but I understand that cannot work as a default.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
PostPosted: Mon Nov 05, 2012 4:41 pm    Post subject: Reply with quote
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521
Back to top
View user's profile Send private message
wdhenrik



Joined: 19 Jul 2012
Posts: 5
PostPosted: Tue Nov 06, 2012 4:12 pm    Post subject: Reply with quote
Thank you again Red Gate for inadvertantly revealing a problem with a database. Very Happy

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. Mad

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

Problem solved.

Thanks again,
Wes
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
PostPosted: Wed Nov 07, 2012 5:09 pm    Post subject: Reply with quote
Hello Wes,

Thanks for following up. I'll deactivate the support issue.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group