Sequenced Foreign Key

Last time I wrote about temporal databases, I had used a mostly-unaltered copy of the well-known AdventureWorks database sample. I thought it was best to base any material I present on that, because it is widespread and does not need much introduction. I see now that I will not be able to stick to that much longer because, as far as temporal data are concerned, AdventureWorks is quite limited out of the box. However, I do not have the time right now to prepare any other sample of significant size, so I will continue using it, for the time being, and explain where it is lacking. And one very crucial thing it has that gets in the way of demonstrating temporal databases is autonumbering columns.

Cue the concept of a sequenced Primary Key. Like the Primary Key in a conventional database (called a Snapshot database when viewed from within a temporal setting), a sequenced Primary Key defines the identity of the data items (notice I don’t call them rows), and is unique among all items — but this time, it unique for each moment in time. Which means that, in the general case, a single data item can change through time and actually be represented  by several rows of the relation (and that’s why I did not call items rows to begin with). Autonumbering columns (IDENTITY columns, as they’re called in Transact SQL) are a decent way to provide automatic surrogate keys in snapshot relations, but they make it impossible to use the table as-is to hold multiple versions of an item, which means I either have to start twiddling more with the AdventureWorks schema (and end up having to create a migration script), or skip the sequenced Primary Key for a while and jump directly to the sequenced Foreign Key.

The sequenced Foreign Key, as you have probably already guessed, is a reference from one relation to another that is defined for each moment in time. And this means that, time being as vast as it is, one data item (let’s call them objects to simplify things), one object in the first relation can reference different objects from the second in different moments in time. But, just like in its snapshot equivalent, a sequenced foreign key can only reference sequenced primary keys that exist for the moments of time involved.

Transposing it to my mostly-unaltered AdventureWorks database lacks a bit of generality, because each object is only defined for a single validity period. Trust me, having multiple, non-coinciding (and possibly non-contiguous) validity intervals in both relations makes everything a little more exciting (read as: vastly more complicated), but the essence of the sequenced Foreign Key will shine through, even with this example.

For this example, I’ll “upgrade” the Foreign Key which exists from the Production.WorkOrder relation to the Production.Product relation. I chose to use SellStartDate to SellEndDate as the validity period of the product. It does not mean that the creators of the AdventureWorks schema had that interpretation in mind, but this interpretation of the validity period can be used to make my point (and there are no other columns in the table, anyway). After adding the validity period columns to Production.Product and making it a State relation, like I did to the Production.WorkOrder in my previous post on Temporal databases, I declare the integrity constraints. I actually had to modify Pandamator to allow that, because it used to ignore IDENTITY columns altogether, since they were antithetical to the presence of real sequenced relations.

alter table Production.Product add validtime constraint PK primary key(ProductID);

alter table Production.WorkOrder add validtime constraint PK primary key(WorkOrderID);

alter table Production.WorkOrder add validtime constraint FK_Product foreign key(ProductID) references Production.Product on delete restrict;

After declaring them, I create the temporal artifacts.

alter validtime

Of interest, is procedure Production.ChkFK_Product_WorkOrder_To_Product, which exists to be used automatically in the case of modifications that can violate the Foreign Key. Since my computed columns for the validity intervals are not updateable, you can’t actually try to update anything (Sql Server is smart enough to decline running the update to begin with), but you can trigger the check if you do a dummy update in Sql Management Studio.

UPDATE Production.Product SET Name = Name WHERE ProductID = -1

Just something to invoke the trigger. You’ll get back an error like the following.

Msg 50000, Level 16, State 2, Procedure TR_FK_Product_WorkOrder_To_Product_R, Line 15
Transaction violates sequenced referential constraint for FK_Product(RESTRICT) Production.WorkOrder to Production.Product, e.g. Production.WorkOrder.WorkOrderID=72587 ValidFromDate="Jul  3 2004 12:00AM" ValidToDate="Jul 14 2004 12:00AM"
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

What this says, is that the foreign key is violated (it is actually invalid to begin with, as Pandamator does not currently check integrity constraints at the time of the alter validtime command) and an example of the violation is a WorkOrder with WorkOrderId=72587 between July 3rd, 2004 and July 14th, 2004, which is the whole validity period for that WorkOrder, if you check it. Which makes sense, since the Product with ProductId=804, is only valid between July 1st, 2002 and June 30th, 2003! We have uncovered inconsistencies between the validity intervals in Products and WorkOrders of the AdventureWorks database! (Don’t take that too literally, as the interpretations I’m using do not actually exist in the original database).

In a different article, I may go into more depth into what the sequenced Foreign Key update rules mean (no, there are no delete rules in Pandamator, as I don’t currently allow updating the sequenced Primary Key). I also promise to spend some time treating how the validity interval works in Pandamator, and how I’ve not been completely honest in interpreting the AdventureWorks datetime columns as validity endpoints.

Advertisements

One thought on “Sequenced Foreign Key

  1. Pingback: What’s in a Validity Interval? | dsouflis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s