Thread: Temporal foreign keys

Hey,

how can I implement temporal foreign keys with postgresql? Is writing
triggers the only way to enforce temporal referential integrity
currently?

-Matthias

On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> Hey,
>
> how can I implement temporal foreign keys with postgresql? Is writing
> triggers the only way to enforce temporal referential integrity
> currently?

Yes, currently that's the only way. Look at CREATE CONSTRAINT TRIGGER.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> wrote:

> On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> > Hey,
> >
> > how can I implement temporal foreign keys with postgresql? Is writing
> > triggers the only way to enforce temporal referential integrity
> > currently?
>
> Yes, currently that's the only way. Look at CREATE CONSTRAINT TRIGGER.

It works in 9.2devel ;-)

test=# create table x (d daterange primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
for table "x"
CREATE TABLE
test=*# create table y (d daterange references x);
CREATE TABLE
test=*# insert into x values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-10)');
INSERT 0 1
test=*# insert into y values ('[2012-01-01,2012-01-20)');
ERROR:  insert or update on table "y" violates foreign key constraint "y_d_fkey"
DETAIL:  Key (d)=([2012-01-01,2012-01-20)) is not present in table "x".



Jeff: thx for YOUR work!


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Fri, 2012-03-16 at 15:13 +0100, Andreas Kretschmer wrote:
> > On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> > > how can I implement temporal foreign keys with postgresql? Is writing
> > > triggers the only way to enforce temporal referential integrity
> > > currently?
> >
> It works in 9.2devel ;-)
>
> test=# create table x (d daterange primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
> for table "x"
> CREATE TABLE
> test=*# create table y (d daterange references x);
> CREATE TABLE
> test=*# insert into x values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-20)');
> ERROR:  insert or update on table "y" violates foreign key constraint "y_d_fkey"
> DETAIL:  Key (d)=([2012-01-01,2012-01-20)) is not present in table "x".

If I understand what he was asking for, it was a kind of "range foreign
key" which means that the following query should succeed:

  insert into y values ('[2012-01-02,2012-01-04)');

because that range is contained in a value in the table x.

So it's slightly different semantics than a normal foreign key.

But yes, normal foreign keys (based on equality) work fine over range
types.

Regards,
    Jeff Davis

On Fri, Feb 3, 2012 at 6:58 AM, Matthias <nitrogenycs@googlemail.com> wrote:

> how can I implement temporal foreign keys with postgresql? Is writing
> triggers the only way to enforce temporal referential integrity
> currently?

I think you need to explain what you want slightly better.

My guess would be you want this

  create table x (d daterange primary key);
  create table y (e date references x (d));

which is a lookup to show that the date is within a valid date range.

But you may also want this...

  create table x (id integer, d daterange, primary key(id, d));
  create table y (id integer, xid integer, e date, foreign key (xid,
e) references x (id, d));

which is to locate the valid row within a temporal lookup table.

Neither is possible, as yet.

Or you might want something entirely different?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services