I've built current sources on my brand new linux box,
thought I'd try foreign key constraints since the datamodel for
the ArsDigita Community System contains hundreds of them.
Figured this might provide a bit of a stress test for the
implementation.

So...what's wrong with the following?

donb=> create table foo(i integer);
CREATE
donb=> create table bar(i integer references foo);
ERROR:  FOREIGN KEY match type UNSPECIFIED not implemented yet
donb=>                                            

This is how I'm used to doing it in Oracle.  I've tried a few
permutations, what am I missing?  My copy of Date is still in
Boston...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sat, 22 Jan 2000 18:04:28 -0800
Don Baccus <dhogaza@pacifier.com>
foreign keys?

>I've built current sources on my brand new linux box,
>thought I'd try foreign key constraints since the datamodel for
>the ArsDigita Community System contains hundreds of them.
>Figured this might provide a bit of a stress test for the
>implementation.
>
>So...what's wrong with the following?
>
>donb=> create table foo(i integer);
>CREATE
>donb=> create table bar(i integer references foo);
>ERROR:  FOREIGN KEY match type UNSPECIFIED not implemented yet
>donb=>                                            
>
>This is how I'm used to doing it in Oracle.  I've tried a few
>permutations, what am I missing?  My copy of Date is still in
>Boston...

As of the last snapshot I downloaded, only MATCH FULL was 
implemented fully and I think postgres will only be happy 
if you specify the column list or table foo has a primary key.

create table bar(i integer references foo(i) match full);

That seems to work, although by the way I read the spec, I'm
not sure that it should since there is no unique constraint 
specified on foo(i).

Sun, 23 Jan 2000 03:17:03 -0500
acroyear@bigpanda.com
Re: [HACKERS] foreign keys? 
At 03:17 AM 1/23/00 -0500, acroyear@bigpanda.com wrote:

>As of the last snapshot I downloaded, only MATCH FULL was 
>implemented fully and I think postgres will only be happy 
>if you specify the column list or table foo has a primary key.

I actually tried creating table foo with and without a primary
key, with no effect.

>create table bar(i integer references foo(i) match full);
>
>That seems to work, although by the way I read the spec, I'm
>not sure that it should since there is no unique constraint 
>specified on foo(i).

Good point, I wonder?  Maybe I should get my girlfriend to
snail-mail me my copy of Date, since I won't be back to Boston
until March.  

Anyway, the following works:

create table foo(i integer primary key);
create table bar(i integer references foo match full);

It finds the primary key within foo to match upon.

Thanks for the help.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sun, 23 Jan 2000 07:35:59 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] foreign keys? 