Using a second table for unique key?

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

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

StackExchange Database Administrators Q#246035, answer score: 4