Tuple Versioning and composite primary key

Question

I have to create a database and have to make sure that we can load the data as it was at a specific date, so I decided to use tuple versioning.

Let's say we have the following two tables:

CREATE TABLE Author
(
  Id UNIQUEIDENTIFIER NOT NULL,
  Firstname VARCHAR(100) NOT NULL,
  Surname VARCHAR(200) NOT NULL,
  ValidFrom DATETIME NOT NULL,
  ValidUntil DATETIME NULL,
  PRIMARY KEY (ID, ValidFrom)
)

CREATE TABLE Book
(
  Id UNIQUEIDENTIFIER NOT NULL,
  Title VARCHAR(100) NOT NULL,
  ISBN VARCHAR(100) NOT NULL,
  AuthorId UNIQUEIDENTIFIER NOT NULL,
  ValidFrom DATETIME NOT NULL,
  ValidUntil DATETIME NULL,
  PRIMARY KEY (Id, ValidFrom)
)

The first time when I enter a new author I will generate a new GUID. I use this GUID in the book table as well to make a reference to the author.

If there is an update on the author, I create a new record with the same GUID, but define the current date as "ValidFrom" and also set the "ValidUntil" from the original record to the current date.

I don't have to change the book table because Author.Id did not change.

The problem I'm facing now is that I would like to add a foreign key constraint on Book.AuthorId = Author.Id

Unfortunately this does not work because I use a composite primary key. I do not want to add the Author.ValidFrom to my Book table because I just want to reference the most recent one and not a specific version.

Any idea on how I can solve this? I think I could add a trigger that makes sure that you can't delete an author if there is already a book recorded, but I have no solution to allow cascade delete.

I'm grateful for every hint or advise.

Answer 1

This works on 2008 (relies on using a MERGE statement to change which row is being referenced by Book atomically). It does introduce new columns, you might want to hide them behind a view:

CREATE TABLE Author
(
  Id UNIQUEIDENTIFIER NOT NULL,
  Firstname VARCHAR(100) NOT NULL,
  Surname VARCHAR(200) NOT NULL,
  ValidFrom DATETIME NOT NULL,
  ValidUntil DATETIME NULL,
  Active as CASE WHEN ValidUntil is null THEN CONVERT(datetime,'99991231',112) ELSE ValidUntil END Persisted
  PRIMARY KEY (ID, ValidFrom),
  UNIQUE (ID,Active)
)
go
CREATE TABLE Book
(
  Id UNIQUEIDENTIFIER NOT NULL,
  Title VARCHAR(100) NOT NULL,
  ISBN VARCHAR(100) NOT NULL,
  AuthorId UNIQUEIDENTIFIER NOT NULL,
  ValidFrom DATETIME NOT NULL,
  ValidUntil DATETIME NULL,
  PRIMARY KEY (Id, ValidFrom),
  FK_Link as CONVERT(datetime,'99991231',112) persisted,
  Foreign key (AuthorID,FK_Link) references Author (Id,Active) on delete cascade
)
go
declare @AuthorId uniqueidentifier
set @AuthorId = NEWID()
insert into Author(Id,Firstname,Surname,ValidFrom)
select @AuthorId,'Boris','McBoris',CURRENT_TIMESTAMP
insert into Book(Id,Title,ISBN,AuthorId,ValidFrom)
select NEWID(),'How to use tuple versioning','12345678',@AuthorId,CURRENT_TIMESTAMP

;with newAuthorInfo as (
    select @AuthorId as Id,'Steve' as Firstname,'McBoris' as Surname,t.Dupl
    from (select 0 union all select 1) t(Dupl)
)
merge into Author a
using newAuthorInfo nai
on
    a.Id = nai.Id and
    a.ValidUntil is null and
    nai.Dupl = 0
when matched then update set ValidUntil = CURRENT_TIMESTAMP
when not matched then insert (Id,Firstname,Surname,ValidFrom)
values (nai.Id,nai.Firstname,nai.Surname,CURRENT_TIMESTAMP);

;with newAuthorInfo as (
    select @AuthorId as Id,'Steve' as Firstname,'Sampson' as Surname,t.Dupl
    from (select 0 union all select 1) t(Dupl)
)
merge into Author a
using newAuthorInfo nai
on
    a.Id = nai.Id and
    a.ValidUntil is null and
    nai.Dupl = 0
when matched then update set ValidUntil = CURRENT_TIMESTAMP
when not matched then insert (Id,Firstname,Surname,ValidFrom)
values (nai.Id,nai.Firstname,nai.Surname,CURRENT_TIMESTAMP);

go
select * from Author
select * from Book

delete from Author where ValidUntil is not null

select * from Author
select * from Book

delete from Author

select * from Author
select * from Book

---

For a pre-2008 solution, I don't think you can do better than triggers. You can introduce a second Author table that does just have the Id column (uniquely), which you can FK against from Book, and cascade delete from that table to Book. Then you just need a delete trigger on Author, such that if you're removing the final row from Author for a particular Author Id, you delete the row from this new table