Competition: What’s your favorite Redgate tool? Enter now.

Incorrect schema change choice to rename a column.

kepr00kepr00 Posts: 43 Bronze 2
edited February 19, 2016 3:16PM in SQL Compare 11
This is the scenario.
I have a table called myTable that originally had an INT column called AccountID
There was a foreign key to the Account table.

I made a schema change to Drop the AccountID column and add a CountryID INT column.
I also created a foreign key to the Country table.

I took a snapshot of the new schema and tried to apply it to a different database.
When I ran the schema comparison to apply the update to a different database, I received a foreign key exception.

When I reviewed the TSQL that SQL Compare had chosen to make my schema changes I found the following line.
EXEC sp_rename N'[dbo].[myTable].[AccountID]', N'CountryID', 'COLUMN'
Because the AccountID column originally had AccountIDs in it and not CountryIDs I received the foreign key error when the rename was attempted.

The question is, why SQL Compare chose a rename operation rather than dropping AccountID and adding CountryID which I feel is really the only sane decision the API could make?
Steve T

Comments

  • Hi Steve,

    It looks like SQL Compare automatically mapped the AccountID and CountryID columns. If two columns with different names are of the same type and are at the same position in the column order, then SQL Compare will map them.

    In this case, this behaviour is undesirable so you'll need to unmap them. To do this, you need to click on "Edit Project...", then go to the "Table Mapping" tab. In the status column, where it says "Mapped", click on the dropdown to bring up the column mapping window. Select the row where AccountID and CountryID are mapped, then press the "Unmap" button. Now when you generate a deployment script, SQL Compare will drop the AccountID column and add the CountryID column instead of doing a rename.
    Software Engineer
    Redgate Software
  • I too experienced the same behavior where a column was mapped unexpectedly. The data type was the same, but the name was wildly different. This caused issues as the renamed column contained data which was carried forward. My expected behavior was that the old column would be dropped and the new one created with a NULL default.

    Is it possible to disable the rename option for the project, without having to notice that something "undesirable" is going on?

    I would comment that this rename did not show on the Warnings tab of the Deployment Wizard.

    Cheers

    Colin
  • Just noticed that a Yellow bar appeared at the top of the window where you select the tables you wish to deploy
    .
    :!: 1 of N Warnings: [Schema].[TableName] columns were mapped automatically

    I will look out for that.
Sign In or Register to comment.