元组版本控制和复合主键

Question

我必须创建一个数据库，并确保我们可以加载特定日期的数据，所以我决定使用 元组版本控制。

假设我们有以下两个表：

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)
)

当我第一次输入新作者时，我将生成一个新的 GUID。我也在图书表中使用这个 GUID 来作为作者的参考。

如果作者有更新，我将创建具有相同 GUID 的新记录，但将当前日期定义为“ValidFrom”，并将原始记录中的“ValidUntil”设置为当前日期。

我不必更改图书表，因为 Author.Id 没有更改。

我现在面临的问题是我想在 Book.AuthorId = Author.Id 上添加外键约束，

不幸的是这不起作用，因为我使用复合主键。我不想将 Author.ValidFrom 添加到我的 Book 表中，因为我只想引用最新的版本而不是特定版本。

我知道如何解决这个问题吗？我想我可以添加一个触发器，确保如果已经记录了一本书，则无法删除作者，但我没有允许级联删除的解决方案。

我很感激每一个提示或建议。

Answer 1

这适用于 2008 年（依赖于使用 MERGE 语句以原子方式更改 Book 引用的行）。它确实引入了新列，您可能希望将它们隐藏在视图后面：

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

---

对于 2008 年之前的解决方案，我认为您无法比触发器做得更好。您可以引入第二个 Author 表，该表只有 Id 列（唯一），您可以从 Book 中 FK 列，然后从该表到 Book 进行级联删除。然后，您只需要在作者上有一个删除触发器，这样，如果您要从作者中删除特定作者 ID 的最后一行，则可以从这个新表中删除该行