Friday 27 May 2011

Temporal Tables - Foreign Keys - Part 1 of ...

Once you start building Temporal Tables in your database, you inevitably start thinking about foreign key constraints - how can they work?


There are so many variations available. For today's post, we'll build a non-temporal table (T2) that references a temporal table (T1/_T1). We want that, if there's a referencing row in T2, that deleting the corresponding row from T1 should not be allowed. Nor should it be possible to insert a row into T2 if there's no corresponding row in T1 - either the row doesn't exist, or it has never existed.


We'll build T1 in an identical fashion to how we built T2 in the previous post. I'll include the code for it at the end of this post also.


First, we have to augment our backing table slightly - we need something that can uniquely distinguish Active, undeleted rows. We're looking for something we can apply a unique index against (together with ID) so that it can be the target of the foreign key constraint. ValidTo, by itself, isn't suitable, since the row we'd want to target has a null value - and nulls in foreign keys mean the constraint isn't checked.


My first thought when building this is to add a computed column:


--This DOES NOT WORK:
alter table dbo._T1 add Active as CASE WHEN ValidTo is null and Deleted = 0 THEN 1 ELSE 0 END persisted

The reason that this isn't going to work is when we go to create the index. We'd like to only add rows with Active=1 to the index (there are going to be lots with Active=0), which sounds like a job for a filtered index. But filtered indexes can't be the target of a foreign key constraint. So we need something that's going to be unique across all rows:


alter table dbo._T1 add ActiveEndDate as CASE WHEN ValidTo is null and Deleted = 0 THEN CONVERT(datetime2,'99991231',112) ELSE ValidTo END persisted
go
create unique index UQ_T1_Active on dbo._T1 (ID,ActiveEndDate)

We have to specify the conversion code, because otherwise, SQL Server thinks that it's non-deterministic - it doesn't analyse the string, nor convert it to datetime once - it performs this conversion each time the computed column is computed.


Now, we can introduce our non-temporal table that references T1:


create table dbo.T2 (
ID int not null,
T1ID int not null,
Val3 varchar(10) not null,
T1Active as CONVERT(datetime2,'99991231',112) persisted,
constraint PK_T2 PRIMARY KEY (ID),
constraint FK_T2_T1 FOREIGN KEY (T1ID,T1Active) references dbo._T1 (ID,ActiveEndDate)
)

Relatively straightforward - other than having to add a computed column here (we could have used a plain column and check constraint, but either way, we need the same fixed value that's going to be present in T1 for active rows.


Finally, we can use T1 and T2... as if nothing odd was happening here


insert into dbo.T1(ID,Val1,Val2)
select 1,'abc','def' union all
select 2,'ghi',null
go
update dbo.T1 set Val1 = Val1 + '_ex'
go
insert into dbo.T2 (ID,T1ID,Val3)
select 1,2,'jkl'
go
update dbo.T1 set Val1 = SUBSTRING(Val1,1,3) + '_ex2'
go
delete from dbo.T1 where ID = 1

Everything works as expected - we can delete the row where ID=1, because it's not being referenced. But this provokes an error:


delete from dbo.T1 where ID = 2

Msg 547, Level 16, State 0, Procedure T_T1_D, Line 6
The MERGE statement conflicted with the REFERENCE constraint "FK_T2_T1". The conflict occurred in database "Flange", table "dbo.T2".
The statement has been terminated.

As does:


insert into dbo.T2 (ID,T1ID,Val3)
select 2,3,'mno'

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_T2_T1". The conflict occurred in database "Flange", table "dbo._T1".
The statement has been terminated.

And...


insert into dbo.T2 (ID,T1ID,Val3)
select 3,1,'pqr'

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_T2_T1". The conflict occurred in database "Flange", table "dbo._T1".
The statement has been terminated.

So, this is all working as expected.




The setup code for T1 (I've not included the historic function at this time, since we don't need it here)


create table dbo._T1 (
ID int not null,
Val1 varchar(10) null,
Val2 varchar(10) null,

Deleted bit not null,
ValidFrom datetime2 null,
ValidTo datetime2 null,
constraint UQ_T1_ValidFrom UNIQUE CLUSTERED (ID,ValidFrom),
constraint UQ_T1_ValidTo UNIQUE (ID,ValidTo),
constraint FK_T1_Prev FOREIGN KEY (ID,ValidFrom) references dbo._T1 (ID,ValidTo),
constraint FK_T1_Next FOREIGN KEY (ID,ValidTo) references dbo._T1 (ID,ValidFrom),
constraint CK_T1_NoTimeTravel CHECK (ValidFrom < ValidTo),
constraint CK_T1_Val1_NotNull CHECK (
Deleted = 1 or Val1 is not null),
constraint CK_T1_Deletion CHECK (
Deleted = 0 or Val1 is null)
)
go
create view dbo.T1
with schemabinding
as
select
ID,
ISNULL(Val1,'') as Val1,
Val2
from
dbo._T1
where
ValidTo is null and
Deleted = 0
go
create unique clustered index PK_T1 on dbo.T1 (ID)
go
create trigger T_T1_I
on dbo.T1
instead of insert
as
set nocount on
;with Dup as (
select i.ID,i.Val1,i.Val2,t.Dupl,CASE WHEN t1.ID is null THEN 0 ELSE 1 END as Existing
from
inserted i
left join
dbo._T1 t1
on
i.ID = t1.ID and
t1.Deleted = 1 and
t1.ValidTo is null
cross join
(select 0 union all select 1) t(Dupl)
)
merge into dbo._T1 t1
using Dup on t1.ID = Dup.ID and t1.ValidTo is null and Dup.Dupl = 0
when matched then update set ValidTo = SYSDATETIME()
when not matched and Dupl = 1 then
insert (ID,Val1,Val2,Deleted,ValidFrom)
values (Dup.ID,Dup.Val1,Dup.Val2,0,CASE WHEN Dup.Existing=1 THEN SYSDATETIME() END);
go
create trigger T_T1_U
on dbo.T1
instead of update
as
set nocount on
;with Dup as (
select i.ID,i.Val1,i.Val2,t.Dupl
from
inserted i
cross join
(select 0 union all select 1) t(Dupl)
)
merge into dbo._T1 t1
using Dup on t1.ID = Dup.ID and t1.ValidTo is null and Dup.Dupl = 0
when matched then update set ValidTo = SYSDATETIME()
when not matched then
insert (ID,Val1,Val2,Deleted,ValidFrom)
values (Dup.ID,Dup.Val1,Dup.Val2,0,SYSDATETIME());
go
create trigger T_T1_D
on dbo.T1
instead of delete
as
set nocount on
;With Dup as (
select d.ID,t.Dupl
from
deleted d
cross join
(select 0 union all select 1) t(Dupl)
)
merge into dbo._T1 t1
using Dup on t1.ID = Dup.ID and t1.ValidTo is null and Dup.Dupl = 0
when matched then update set ValidTo = SYSDATETIME()
when not matched then
insert (ID,Deleted,ValidFrom)
values (Dup.ID,1,SYSDATETIME());
go

2 comments:

  1. more parts??

    solution ?

    https://stackoverflow.com/questions/4491411/sql-server-failover-cluster-determine-active-node?rq=1

    ReplyDelete
  2. Hello, Is there posibility to contact you via Skype for instance?

    ReplyDelete