Temporal foreign keys

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