Versionamento da tupla e chave primária composta

9

Eu tenho que criar um banco de dados e ter certeza de que podemos carregar os dados como estavam em uma data específica, então eu decidi usar versionamento de tupla .

Digamos que temos as duas tabelas a seguir:

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

Na primeira vez que eu inserir um novo autor, gerarei um novo GUID. Eu uso esse GUID na tabela de livros, além de fazer uma referência ao autor.

Se houver uma atualização no autor, crie um novo registro com o mesmo GUID, mas defina a data atual como "ValidFrom" e defina também o "ValidUntil" do registro original para a data atual.

Eu não tenho que mudar a tabela de livros porque o Autor.Id não mudou.

O problema que estou enfrentando agora é que eu gostaria de adicionar uma restrição de chave estrangeira em Book.AuthorId = Author.Id

Infelizmente isso não funciona porque eu uso uma chave primária composta. Eu não quero adicionar o Author.ValidFrom à minha tabela Book porque eu quero apenas referenciar o mais recente e não uma versão específica.

Alguma ideia de como posso resolver isso? Eu acho que poderia adicionar um gatilho que garante que você não pode excluir um autor se já houver um livro gravado, mas não tenho solução para permitir a exclusão em cascata.

Sou grato por todas as dicas ou conselhos.

    
por MFox 20.05.2011 в 16:09
fonte

1 resposta

3

Isso funciona em 2008 (depende do uso de uma instrução MERGE para alterar qual linha está sendo referenciada por Book atomicamente). Ele introduz novas colunas, você pode querer ocultá-las por trás de uma visualização:

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

Para uma solução anterior a 2008, não acho que você possa fazer melhor do que os gatilhos. Você pode introduzir uma segunda tabela Autor que tenha apenas a coluna Id (exclusivamente), que você pode usar contra o Book, e excluir em cascata dessa tabela para o Book. Em seguida, você só precisa de um gatilho de exclusão no Autor, de modo que, se estiver removendo a linha final do Autor de um ID de Autor específico, exclua a linha dessa nova tabela

    
por Damien_The_Unbeliever 20.05.2011 / 16:45
fonte