Re: Temporal Table Relations and Referential Integrity

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: SQL Postgresql List <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Temporal Table Relations and Referential Integrity
Date: 2007-05-14 17:00:36
Message-ID: 245961.91228.qm@web31805.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


--- Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
> It'd be helpful if you provided links to what you refer to as the
> Current style. The only reference for managing temporal data in ANSI
> SQL I'm aware of is Snodgrass' "Developing Time-Oriented Database
> Applications in SQL"[1]. Date, Darwen, and Lorentzos' "Temporal Data
> and the Relational Model"[2] is a more general text on the same
> topic, but isn't directly applicable to ANSI SQL.

The "Current style" should actually be called "Current State". There are three temporal models
discussed in the Snodgrass book: Current, Sequenced, and Non-Sequenced State Tables. This is
described in Chapter 7 sections 1-3. The Current State table only models currently known and
historical values of the database. Sequenced and Non-Sequenced can model past/present/future
data.

The temporal relationship that I am interested in modeling is a temporal parent that is related to
a temporal child. I wanted to see if any method exists to support Referential Integrity that can
handle Temporal ON UPDATE CASCADE and ON DELETE CASCADE between the parent and child tables.

Snodgrass page 127 code fragment 5.20 uses a "CREATE ASSERTION" to maintain RI for CURRENT STATE
data between a temporal parent and child. However, I am looking for a method to extend this
example to allow ON UPDATE CASCADE and ON DELETE CASCADE, since the CREATE ASSERTION method will
only prevent you from from altering the data in a way so as to make it inconsistent between the
two tables.

> [2](http://www.amazon.com/gp/product/1558608559/)
I did purchase the Chris Date book, but I haven't spend any time reading it since its
illustrations use Tutorial-D instead of SQL.

Regards,
Richard Broersma Jr.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Joost Kraaijeveld 2007-05-14 18:02:11 How to retrieve a n-ary tree in SQL?
Previous Message Michael Glaesemann 2007-05-14 16:10:02 Re: Temporal Table Relations and Referential Integrity