SQLCompare updating column with FK to new table

corkhead · 24 Oct 2013 at 14:22

Hi Guys

We use RedGate SQLCompare over the cmd line to update our databases everyday.

Ive added a column to an existing table that doesn't allow nulls and has a default value of 1.

The FK for this column is to a new lookup table with static data.

When SQLCompare updates our database it tries to add the column and default value before adding the new lookup table and so the FK breaks and the update fails

Anyone have any experience with this?

Is some kind of pre migration the only way around this?

thanks!

GravyMonster · 24 Oct 2013 at 14:34

This is a common problem with RedGate tools. The RedGate SQL source control software has the same issue of trying to create the FK constraint on an existing table, before the new static data has been inserted.

Redgates support are ******* useless 90% of the time, so we end up fixing it ourselves manually. Usually creating a new DB alongside the existing, importing data into that, writing some SQL to copy the data over, then run the SQLCompare to add the FK.

corkhead · 24 Oct 2013 at 15:38

That's what i was afraid of

corkhead · 25 Oct 2013 at 08:30

I managed to get it to work by calling the sqlcompare software twice...

Once with the /IgnoreKeys cmd switch to get just the new schema without FK's

Then another call normally to get the FK's back

This seems to fix it but means i have to call it twice

GravyMonster · 25 Oct 2013 at 11:12

That sounds about the nicest solution. This has been an issue for us for at least 2 years, and we've raised numerous bugs and support requests to no avail. I honestly don't know how RedGate managed to F up such BASIC thing.