Hi, Far as I am aware - there is not yet described FK feature...
But should be possible to ensure that rule via trigger.. 1 after update on container, and one after insert/update on item... Kind Regards, Misa On Saturday, April 6, 2013, Matthias Nagel wrote: > Hello, > this is a re-post from the SQL user list 2 month ago, because I assume > only a developer can answer the questions below. > Thanks, Matthias Nagel > > > ---------- Weitergeleitete Nachricht ---------- > > Betreff: Range types (DATERANGE, TSTZRANGE) in a foreign key with > "inclusion" logic > Datum: Mittwoch 23 Januar 2013, 11:28:10 > Von: Matthias Nagel <matthias.h.na...@gmail.com <javascript:;>> > An: pgsql-...@postgresql.org <javascript:;> > > Hello everybody, > > first a big thank you to all that make the range types possible. They are > great, especially if one runs a database to manage a student's university > dormitory with a lot of temporal information like rental agreements, room > allocations, etc. At the moment we are redesigning our database scheme for > PosgreSQL 9.2, because the new range types and especially the "EXCLUSION" > constraints allow to put a lot more (business) logic into the database > scheme than before. > > But there is one feature missing (or I am too stupid to find it). > > Let's say we have some kind of container with a lifetime attribute, i.e. > something like that > > CREATE TABLE container ( > id SERIAL PRIMARY KEY, > lifetime DATERANGE > ); > > Further, there are items that must be part of the container and these > items have a lifetime, too. > > CREATE TABLE item ( > id SERIAL PRIMARY KEY, > container_id INTEGER, > lifetime DATERANGE, > FOREIGN KEY (container_id) REFERENCES container ( id ), > EXCLUDE USING gist ( container_id WITH =, lifetime WITH && ) > ); > > The foreign key ensures that items are only put into containers that > really exist and the exclude constraint ensure that only one item is member > of the same container at any point of time. > > But actually I need a little bit more logic. The additional contraint is > that items must only be put into those containers whose lifetime covers the > lifetime of the item. If an item has a lifetime that exceeds the lifetime > of the container, the item cannot be put into that container. If an item is > already in a container (with valid lifetimes) and later the container or > the item is updated such that either lifetime is modified and the contraint > is not fullfilled any more, this update must fail. > > I would like to do someting like: > > FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table ( id, > lifetime ) USING gist ( container_id WITH =, lifetime WITH <@ ) > > (Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear > what I want.) > > So, now my questions: > > 1) Does this kind of feature already exist in 9.2? If yes, a link to the > documentation would be helpful. > > 2) If this feature does not directly exist, has anybody a good idea how to > mimic the intended behaviour? > > 3) If neither 1) or 2) applies, are there any plans to integrate such a > feature? I found this discussion > http://www.postgresql.org/message-id/4f8bb9b0.5090...@darrenduncan.net . > Does anybody know about the progress? > > Having range types and exclusion contraints are nice, as I said in the > introdruction. But if the reverse (foreign key with inclusion) would also > work, the range type feature would really be amazing. > > > Best regards, Matthias Nagel > > > > ---------------------------------------------------------------------- > Matthias Nagel > Willy-Andreas-Allee 1, Zimmer 506 > 76131 Karlsruhe > > Telefon: +49-721-8695-1506 > Mobil: +49-151-15998774 > e-Mail: matthias.h.na...@gmail.com <javascript:;> > ICQ: 499797758 > Skype: nagmat84 > > ------------------------------------------------------------- > ---------------------------------------------------------------------- > Matthias Nagel > Willy-Andreas-Allee 1, Zimmer 506 > 76131 Karlsruhe > > Telefon: +49-721-8695-1506 > Mobil: +49-151-15998774 > e-Mail: matthias.h.na...@gmail.com <javascript:;> > ICQ: 499797758 > Skype: nagmat84 > > > > -- > Sent via pgsql-hackers mailing list > (pgsql-hackers@postgresql.org<javascript:;> > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
