Sql-server – Is it necessary to drop foreign keys before adding a null column to a table and then re-add them

foreign keysql-server-2008

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?.

Best Answer

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.

Related Question