Sql-server – Using a second table for unique key

database-designprimary-keysql serversql server 2014

This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s.

What's perhaps the most important table in the system, dbo.ACADEMIC, has only a clustered primary key made up of multiple columns. I need to be able to SELECT and UPDATE rows on dbo.ACADEMIC via an immutable key such as a GUID, but I also can't alter the table.

There are many reasons I can't modify the structure of this table, but chief among them is that one school tried it and broke the desktop client software.

Here's a simplified sample of ACADEMIC's primary key:

PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
-------------- ------------- ------------- ----------
P000011111     1996          SPRING        URBAN
P000022222     2012          SPRING        HCSMGT
P000033333     2002          SUMMER        CHEMIS
P000044444     2015          FALL          SEDUCA
P000055555     1983          SUMMER        POLSCI

Some columns that are part of the key change a lot — when a student changes curriculum, for example. Integrating with other systems is very hard because of this lack of an immutable id.

Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC? Then a view could be constructed that would show all the data from ACADEMIC plus the id column; integration projects could use this view.

Proposed custom.AcademicKey table:

id                                   PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
------------------------------------ -------------- ------------- ------------- ----------
956EB195-C7C3-4E47-9A1B-0000191B95A2 P000011111     1996          SPRING        URBAN
FE00D2F4-5F5E-45D2-AB63-00002CC92F40 P000022222     2012          SPRING        HCSMGT
C1AF44CD-DDD1-4D46-A19E-0000A841B70F P000033333     2002          SUMMER        CHEMIS
D4E8FC3F-CECC-4195-9924-0000B9BC33A7 P000044444     2015          FALL          SEDUCA
456B16ED-C504-456B-A2E5-00019F244A95 P000055555     1983          SUMMER        POLSCI

Would a foreign key tied to the clustered key on ACADEMIC accomplish this?

Would triggers have to be added to ACADEMIC in order to populate new rows into AcademicKey?

Best Answer

Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC?

Yes. Minimally you need a Foreign Key and an AFTER INSERT trigger.

eg

use tempdb
go
--create schema custom
go

--drop table if exists custom.AcademicKey
--drop table if exists dbo.Academic
go
create table dbo.Academic
(
   PEOPLE_CODE_ID varchar(20), 
   ACADEMIC_YEAR int, 
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
)
create table custom.AcademicKey
(
   Id uniqueidentifier default (newsequentialid()),
   PEOPLE_CODE_ID varchar(20), 
   ACADEMIC_YEAR int, 
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key nonclustered (id),
   constraint ak_AcademicKey
     unique clustered (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM),
   constraint fk_AcademicKey_Adademic
     foreign key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     references Academic (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     on update cascade
     on delete cascade
)

go

create or alter trigger Academic_CreateAcademicKey 
  on dbo.Academic after insert
as
begin
    set nocount on;

    insert into custom.AcademicKey(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
    select PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM
    from inserted;
end

go 

insert into Academic(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
values ('P000011111',     1996,          'SPRING',      ' URBAN')

select * from custom.AcademicKey

update Academic set CURRICULUM = 'CHEMIS' where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey

delete from Academic where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey
Related Question