FK Column renamed when referencing a different table

software-programmer

I have a scenario that is easy to reproduce, but feel the behaviour is not correct - I'm happy to listen to alternative suggestions or why this is the way it is.

In my TEST environment I have two table definitions, User and Role. User has a column RoleId, that is an FK to the Id column in the Role table. I then populate this with data.

In the meantime, another developer decides to create a Group table in the DEV environment and change the FK in the User table to be called GroupId and points to the Group table.

The problem is I can never deploy this change using SQL Compare as it generates

EXEC sp_rename N'[dbo].[User].[RoleId]', N'GroupId', N'COLUMN'

When running this against TEST it will fail with FK violations as there's no data in the newly deployed Group table.

Are there any switches to force this to comply, or do I have to approach this in a phased manner such as

1. Drop FK constraint

2. Add new column and FK

3. Drop old column

Your feedback is greatly appreciated.

Sergio R

Hi,

I've been testing this and in this case the way to go would be to create a deployment script, unfortunately there isn't a switch that would allow you to ignore this.

Thank you,

software-programmer

How would we manage this as we are using SQL Source Control + DLM Automation Suite for our deployments (TeamCity + Octopus Deploy)

Sergio R

Hi,

In that case it is not as straightforward.

Maybe you could disable the foreign key and only enable it once the manual data changes have been made?
If that's not an option here's something else you can try:
- Add a table named Group, add GroupId column to User table and related that column with Id column in Group table. Don't drop RoleId column. GroupId column might need to be nullable here.
- Deploy these changes.
- Handle necessary data changes manually for User table to relate the users with Groups now and add data to Groups table, too.
- In dev, drop the RoleId column in User table.
- Deploy the changes.
- Now, you can try to make the GroupId column not nullable and deploy this change.

Thank you,