Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.