Geek Answers Handbook

How to add a surrogate key to related tables?

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

Note. These are not real table names.

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

JakToRoad (JakMasterId, Date) (FK)

JakToBig (JakMasterId, Date) (FK)

What steps should be taken to add a surrogate key to these three tables so that the new keys correctly refer to each other?

Thanks!

+4
source share
2 answers

I assume that you want to replace JakMasterId with an auto-increment field so that there are no varchar (60) fields in the other two tables and to improve query time, but you keep 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
+3
source

You may be able to do this in three steps in the database to create a surrogate PK

  • Modify the table to create a surrogate key column. In addition, it must be zero.

  • Write a small program for setting key values. This is a loop doing UPDATE.

  • Modify the table to make the surrogate key column non-zero, auto-increment, indexed, unique, etc.

Now you need to create FK.

  • Modify the table to add the FK column. Again, it should be zero.

  • Write a small program to set the FK column. This is SELECT (to get a PK row based on non-surrogate keys) and UPDATE in the link table.

  • Modify the table as necessary to make FK non-zero. This is not always necessary, it depends on determining the relationship of the FK table to the PK table.

Repeat the FK creation for all other tables.

+1
source

More articles:


All Articles