I have generated some SQL Server(2008) upgrade scripts using SqlCompare. The primary objective is to add a null column. However, the generated script wants to drop foreign keys in a couple of other tables and the primary key of the target table before the add column. It then re-adds them. Is this necessary? What is the reasoning behind doing it?.
No it is not necessary. See this working example below:
use TestDatabase; go -- create the parent table (houses the PK) create table dbo.ParentTable ( ParentId int identity(1, 1) not null constraint PK_ParentTable_ParentId primary key clustered, some_int int not null ); go -- insert some dummy data insert into dbo.ParentTable(some_int) values (5), (4), (3); go -- create the child table (houses the FK) create table dbo.ChildTable ( ChildId int identity(1, 1) not null, ParentId int not null constraint FK_ChildTable_ParentId foreign key references dbo.ParentTable(ParentId) ); go -- insert some dummy data insert into dbo.ChildTable(ParentId) values (1), (3); go -- view the contents of each table select * from dbo.ParentTable; select * from dbo.ChildTable; -- add a nullable int column alter table dbo.ParentTable add another_col int null; go -- view the new layout select * from dbo.ParentTable; select * from dbo.ChildTable;
As you can see by the code, I modify
dbo.ParentTable by adding a column. That is a successful operation with the primary key constraint still in place.
As to why your third party software does this, we could guess all day. But most likely they do it to handle some corner case without first testing whether or not the current operation falls into that.