How to add surrogate key to related tables?

Question

I need to add a auto-inc surrogate key to three tables in my data warehouse:

Note: These are not the real table names

JakMaster (JakMasterId, Date) (PK) Note: JakMasterId is varchar(60)

JakToRoad (JakMasterId, Date) (FK)

JakToBig (JakMasterId, Date) (FK)

What steps should I take to add a surrogate key to these three tables so that the new keys reference each other correctly?

Thanks!

Answer 1

You might be able to do this in three steps within the DB to create the surrogate PK

1. Alter the table to create the surrogate key column. Except, it has to be nullable.

2. Write a small program to set the key values. It's a loop doing UPDATEs.

3. Alter the table to make the surrogate key column non-null, auto-increment, indexed, unique, etc.

Now you need to create the FK's.

1. Alter the table to add the FK column. Again, it must be nullable.

2. Write a small program to set the FK column. This is a SELECT (to get the PK row based on non-surrogate keys), and an UPDATE to the referencing table.

3. If necessary, alter the table to make the FK non-null. This isn't always necessary, it depends on the definition of the FK table's relationship with the PK table.

Repeat the FK creation for all other tables.

Answer 2

I'm assuming you are wanting to replace JakMasterId with an auto-increment field so the other two tables don't need a varchar(60) field and to improve query times, but that you are keeping JakMasterId as information.

-- set database single-user

-- drop foreign keys

create table NewMaster (ID int identity(1, 1), JakMasterId, Date))
insert NewMaster(JakMasterId, Date) select JakMasterId, Date from JakMaster
drop table JakMaster
sp_rename 'NewMaster', 'JakMaster'

alter table JakToRoad add MasterId int
alter table JakToBig add MasterId int

update JakToRoad set MasterId = JakMaster.ID
from JakToRoad
inner join JakMaster on JakMaster.JakMasterId = JakToRoad.JakMasterId

update JakToBig set MasterId = JakMaster.ID
from JakToBig 
inner join JakMaster on JakMaster.JakMasterId = JakToBig .JakMasterId

alter table JakToRoad drop column JakMasterId
alter table JakToBig drop column JakMasterId


alter table JakToRoad add constraint FK_JTRtoJM foreign key (MasterId) references JakMaster (ID)
alter table JakToBig add constraint FK_JTBtoJM foreign key (MasterId) references JakMaster (ID)

-- reset database to multi-user