Horton

Thread

  1. foreign keys - script

    Adam Lang <aalang@rutgersinsurance.com> — 2000-08-29T17:12:05Z

    I'm also having a problem with my references.

In the script below, I get this error after each references statement: 
NOTICE:  CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented

Any ideas?

drop table model_part;
drop table models;
drop table parts;
drop table category;
drop sequence model_part_model_part_id_seq;

create table category (
        category_id char(5) primary key,
        name varchar(20));

create table parts (
        part_id char(10) primary key,
        category_id char(5) references category
                on delete cascade,
        name varchar(20),
        price numeric(7,2));

create table models (
        model_id char(10) primary key,
        name varchar(20),
        description text);

create table model_part (
        model_part_id serial,
        part_id char(10) references parts
                on delete cascade,
        model_id char(10) references models
                on delete cascade);

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company

  2. Re: foreign keys - script

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-29T17:50:41Z

    What version are you using?  It wasn't implemented until 7.0
and the script you gave appeared to basically work on my 7.0.2 
machine.  (At least the fk triggers were in pg_trigger after
running it.)

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Adam Lang wrote:

> I'm also having a problem with my references.
> 
> In the script below, I get this error after each references statement: 
> NOTICE:  CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented

  3. Re: foreign keys - script

    Adam Lang <aalang@rutgersinsurance.com> — 2000-08-29T17:59:51Z

    Nope, I'm running 6.5.  Besides upgrading to 7 (I tried once and it was a
pain in the ass.  I'm running RedHat6.2 and I only had about a million
dependencies to update also... one which I never got working...), what can I
do? Technically I really don't need the references, just a nice addition.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Tuesday, August 29, 2000 1:50 PM
Subject: Re: [GENERAL] foreign keys - script


> What version are you using?  It wasn't implemented until 7.0
> and the script you gave appeared to basically work on my 7.0.2
> machine.  (At least the fk triggers were in pg_trigger after
> running it.)
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Tue, 29 Aug 2000, Adam Lang wrote:
>
> > I'm also having a problem with my references.
> >
> > In the script below, I get this error after each references statement:
> > NOTICE:  CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented
>

  4. Re: foreign keys - script

    Ross Reedstrom <reedstrm@rice.edu> — 2000-08-29T18:00:13Z

    On Tue, Aug 29, 2000 at 01:12:05PM -0400, Adam Lang wrote:
> I'm also having a problem with my references.
> 
> In the script below, I get this error after each references statement: 
> NOTICE:  CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented
> 
> Any ideas?
> 

Upgrade to the current PostgreSQL:


test=# create table category (
test(#         category_id char(5) primary key,
test(#         name varchar(20));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
       'category_pkey' for table 'category'
CREATE
test=# 
test=# create table parts (
test(#         part_id char(10) primary key,
test(#         category_id char(5) references category
test(#                 on delete cascade,
test(#         name varchar(20),
test(#         price numeric(7,2));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'parts_pkey'
        for table 'parts'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
test=# 

As to your other question, regarding LEFT JOIN: that's 'not yet
implemented'.  IF you don't have too many of them, or their not too complicated,
you can work around it with a UNION, such as:

SELECT a,b from foo,bar where foo.b = bar.b
UNION
SELECT a,NULL from foo where foo.b is null;

That gives you a LEFT JOIN on foo and bar on field 'b'.

There's currently some discussion going on on HACKERS concerning what
will be needed to make OUTER JOINs happen for 7.1 (current talk is
release sometime in November)

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

  5. Re: foreign keys - script

    mikeo <mikeo@spectrumtelecorp.com> — 2000-08-29T18:15:18Z

    i created my foreign keys afterwards using a statement like this:

Alter table web_hits add constraint fk_webh_wu_id 
            foreign key (wu_id) references web_users(wu_id);

HTH
  mikeo

At 01:12 PM 8/29/00 -0400, Adam Lang wrote:
>I'm also having a problem with my references.
>
>In the script below, I get this error after each references statement: 
>NOTICE:  CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented
>
>Any ideas?
>
>drop table model_part;
>drop table models;
>drop table parts;
>drop table category;
>drop sequence model_part_model_part_id_seq;
>
>create table category (
>        category_id char(5) primary key,
>        name varchar(20));
>
>create table parts (
>        part_id char(10) primary key,
>        category_id char(5) references category
>                on delete cascade,
>        name varchar(20),
>        price numeric(7,2));
>
>create table models (
>        model_id char(10) primary key,
>        name varchar(20),
>        description text);
>
>create table model_part (
>        model_part_id serial,
>        part_id char(10) references parts
>                on delete cascade,
>        model_id char(10) references models
>                on delete cascade);
>
>Adam Lang
>Systems Engineer
>Rutgers Casualty Insurance Company
>

  6. Re: foreign keys - script

    Emile D Snyder <emile@cosource.com> — 2000-08-29T18:39:21Z

    This question was me actually, and thanks for the info!  Is the
development list archived somewhere publicly accessible?

thanks,
-emile

On Tue, 29 Aug 2000, Ross J. Reedstrom wrote:

> As to your other question, regarding LEFT JOIN: that's 'not yet
> implemented'.  IF you don't have too many of them, or their not too
> complicated, you can work around it with a UNION, such as:
> 
> SELECT a,b from foo,bar where foo.b = bar.b
> UNION
> SELECT a,NULL from foo where foo.b is null;
> 
> That gives you a LEFT JOIN on foo and bar on field 'b'.
> 
> There's currently some discussion going on on HACKERS concerning what
> will be needed to make OUTER JOINs happen for 7.1 (current talk is
> release sometime in November)
> 
> Ross
> -- 
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005
>

  7. Re: foreign keys - script

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-29T19:17:54Z

    Hmm... if you have (can get your hands on) the contrib directory from 6.5,
there was a refint package you could work with (although you have to make 
triggers manually i think). I remember people saying there were bugs in
it, but I never played with it so I don't know any details, but there's
probably something in the archives about it.

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Adam Lang wrote:

> Nope, I'm running 6.5.  Besides upgrading to 7 (I tried once and it was a
> pain in the ass.  I'm running RedHat6.2 and I only had about a million
> dependencies to update also... one which I never got working...), what can I
> do? Technically I really don't need the references, just a nice addition.

  8. Re: foreign keys - script

    Adam Lang <aalang@rutgersinsurance.com> — 2000-08-29T19:22:14Z

    Sorry for the off topic question, but what is a "contrib".  I see it a lot
in the rpm directories. (Yes, relatively new to Linux also :P)

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Tuesday, August 29, 2000 3:17 PM
Subject: Re: [GENERAL] foreign keys - script


>
> Hmm... if you have (can get your hands on) the contrib directory from 6.5,
> there was a refint package you could work with (although you have to make
> triggers manually i think). I remember people saying there were bugs in
> it, but I never played with it so I don't know any details, but there's
> probably something in the archives about it.
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Tue, 29 Aug 2000, Adam Lang wrote:
>
> > Nope, I'm running 6.5.  Besides upgrading to 7 (I tried once and it was
a
> > pain in the ass.  I'm running RedHat6.2 and I only had about a million
> > dependencies to update also... one which I never got working...), what
can I
> > do? Technically I really don't need the references, just a nice
addition.
>