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/viewtopic.php?t=9960
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Brian Donahue]

Just following up -- did the cumulative patch (build 55) fix the problem?
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Brian Donahue]

Hi,

This looks like an unrelated problem caused by insufficient rights to decrypt encrypted objects.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521