The ALTER TABLE statement conflicted with the FOREIGN KEY

ricci.s

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

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.

Brian Donahue

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

Just following up -- did the cumulative patch (build 55) fix the problem?

ricci.s

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

Thanks.

ricci.s

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

Brian Donahue

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

Hi,

This looks like an unrelated problem caused by insufficient rights to decrypt encrypted objects.