pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Temporal Table Relations and Referential Integrity 3+ messages / 2 participants [nested] [flat]
* Temporal Table Relations and Referential Integrity @ 2007-05-07 14:59 Richard Broersma Jr <rabroersma@yahoo.com> 2007-05-14 16:10 ` Re: Temporal Table Relations and Referential Integrity Michael Glaesemann <grzm@seespotcode.net> 0 siblings, 1 reply; 3+ messages in thread From: Richard Broersma Jr @ 2007-05-07 14:59 UTC (permalink / raw) To: pgsql-sql 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temporal Table Relations and Referential Integrity 2007-05-07 14:59 Temporal Table Relations and Referential Integrity Richard Broersma Jr <rabroersma@yahoo.com> @ 2007-05-14 16:10 ` Michael Glaesemann <grzm@seespotcode.net> 2007-05-14 17:00 ` Re: Temporal Table Relations and Referential Integrity Richard Broersma Jr <rabroersma@yahoo.com> 0 siblings, 1 reply; 3+ messages in thread From: Michael Glaesemann @ 2007-05-14 16:10 UTC (permalink / raw) To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql 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) ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temporal Table Relations and Referential Integrity 2007-05-07 14:59 Temporal Table Relations and Referential Integrity Richard Broersma Jr <rabroersma@yahoo.com> 2007-05-14 16:10 ` Re: Temporal Table Relations and Referential Integrity Michael Glaesemann <grzm@seespotcode.net> @ 2007-05-14 17:00 ` Richard Broersma Jr <rabroersma@yahoo.com> 0 siblings, 0 replies; 3+ messages in thread From: Richard Broersma Jr @ 2007-05-14 17:00 UTC (permalink / raw) To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql --- 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-05-14 17:00 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-05-07 14:59 Temporal Table Relations and Referential Integrity Richard Broersma Jr <rabroersma@yahoo.com> 2007-05-14 16:10 ` Michael Glaesemann <grzm@seespotcode.net> 2007-05-14 17:00 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox