Options
Incorrect schema change choice to rename a column.
kepr00
Posts: 43 Bronze 2
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?
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
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.
Redgate Software
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
.
:!: 1 of N Warnings: [Schema].[TableName] columns were mapped automatically
I will look out for that.