Обсуждение: Temporal Table Relations and Referential Integrity
In non-temporal tables, changes to the primary key of a Parent table and record deletions can be cascaded to a related Child table using "REFERENCES Parent( Parent_code ) ON DELETE CASCADE ON UPDATE CASCADE," However, since temporal UPDATEs and DELETEs do not behave in the same way as they do in non-temporal tables, is anyone able to successfully use Referential Integrity constraints between Parent and Child tables? Or are custom triggers the only solution to maintain temporal relations between Parents and Children? My question deals specifically with the "Current" style of temporal relations, where only the currently known attributes and attribute histories are stored. As a second question, would anyone know if temporal referential integrity is targeted for future inclusion into the SQL standard? I would be happy to elaborate on what I mean by Temporal Referential-Integrity (RFI) if my questions are unclear. Regards, Richard Broersma Jr.
On May 7, 2007, at 9:59 , Richard Broersma Jr wrote: > However, since temporal UPDATEs and DELETEs do not behave in the > same way as they do in > non-temporal tables, is anyone able to successfully use Referential > Integrity constraints between > Parent and Child tables? Or are custom triggers the only solution > to maintain temporal relations > between Parents and Children? Currently constraint triggers are the only way I know how to get this to work. > My question deals specifically with the "Current" style of temporal > relations, where only the currently known attributes and attribute > histories are stored. 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. > As a second question, would anyone know if temporal referential > integrity is targeted for future > inclusion into the SQL standard? I'm not privy to discussions of the SQL committees :) AIUI, temporal support was included in some of drafts for but it did not make it into the final standard. Snodgrass' book and Darwen & Date's paper "An Overview and Analysis of TSQL2"[3] have some discussion and critique of the draft. > I would be happy to elaborate on what I mean by Temporal > Referential-Integrity (RFI) if my > questions are unclear. I'm not sure what *you* mean by Temporal Referential-Integrity, but I hope the links I've provided help a bit. Michael Glaesemann grzm seespotcode net [1](http://www.cs.arizona.edu/~rts/tdbbook.pdf) [2](http://www.amazon.com/gp/product/1558608559/) [3](http://www.dcs.warwick.ac.uk/~hugh/TTM/OnTSQL2.pdf)
--- Michael Glaesemann <grzm@seespotcode.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.