The ALTER TABLE statement conflicted with the FOREIGN KEY

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

The ALTER TABLE statement conflicted with the FOREIGN KEY

Postby ricci.s » Tue Apr 13, 2010 1:44 pm

Hi everyone,

While using SQL Refactor to rename some columns, I came across the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

This error does not always crop up when the primary key of the table being renamed is an INT, so I was wondering why this error occurs and if there are any solutions round this?

Thanks,

Ricci
ricci.s
 
Posts: 4
Joined: Mon Apr 12, 2010 12:53 pm

Postby ricci.s » Wed Apr 14, 2010 2:07 pm

A quick update,

I noticed that before the insert statement there is no SET IDENTITY_INSERT ON

this isnt always the case as it appears sometimes, but not all the time.

Using the SET IDENTITY_INSERT ON before and SET IDENTITY_INSERT OFF after the insert resolves the problem.
ricci.s
 
Posts: 4
Joined: Mon Apr 12, 2010 12:53 pm

Postby Brian Donahue » Wed Apr 14, 2010 5:20 pm

Hello,

I'd appreciate it if you could try the latest cumulative patch to Refactor. The current release tries to rebuild tables when it doesn't need to -- this stands out in my mind as a reason for the Refactor script trying to insert data when smart rename should be strictly about the schema in most cases.

Here is a link to the information about the patch:
http://www.red-gate.com/MessageBoard/vi ... php?t=9960
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Apr 19, 2010 2:58 pm

Just following up -- did the cumulative patch (build 55) fix the problem?
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby ricci.s » Mon Apr 19, 2010 3:00 pm

I've not updated yet, but once I do I will post if the situation has been resolved.

Thanks.
ricci.s
 
Posts: 4
Joined: Mon Apr 12, 2010 12:53 pm

Postby ricci.s » Mon Apr 19, 2010 4:12 pm

Having tried the patch you suggested, when I renamed a primary key column, i get the following error message displayed:

An error occurred while generating the refactoring: Object reference not set to an instance of an object.. Please contact support@red-gate.com
ricci.s
 
Posts: 4
Joined: Mon Apr 12, 2010 12:53 pm

Postby Brian Donahue » Mon Apr 19, 2010 5:31 pm

Hi,

Can you pelase send the SQL Refactor log to support@red-gate.com and quote F0033501 in the subject line? You can find the log file in %userprofile%\\local settings\\application data\\red gate\\sql refactor\\options
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Fri Apr 23, 2010 9:51 am

Hi,

This looks like an unrelated problem caused by insufficient rights to decrypt encrypted objects.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests