Thread: foreign keys - script
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
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
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 >
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
>
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
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 >
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.
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. >
As for using 7.02, I had tried installing it from scratch, but had dependency problems. I attempted an "upgrade" just now, and here is the list of dependencies failures I had: For Server: postgresql = 7.0.2 is needed by postgresql-server-7.0.2-10 libncurses.so.5 is needed by postgresql-server-7.0.2-10 libpq.so.2.1 is needed by postgresql-server-7.0.2-10 libreadline.so.4.1 is needed by postgresql-server-7.0.2-10 libc.so.6(GLIBC_2.2) is needed by postgresql-server-7.0.2-10 And for base postgresql: libncurses.so.5 is needed by postgresql-7.0.2-10 libreadline.so.4.1 is needed by postgresql-7.0.2-10 postgresql = 6.5.3 is needed by postgresql-devel-6.5.3-6 postgresql = 6.5.3 is needed by postgresql-server-6.5.3-6 libpq.so.2.0 is needed by postgresql-server-6.5.3-6 And this is with 6.5.3 installed. Weird part is, I am sure libc.so.6 is installed. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Adam Lang" <aalang@rutgersinsurance.com> Sent: Tuesday, August 29, 2000 4:29 PM Subject: Re: [GENERAL] foreign keys - script > > Generally outside things that have been contributed, but aren't > sanctioned as being part of the the "core" distribution. It could > just be untested by whoever decides what is and isn't core, or > in postgres' case, often functionality they felt wasn't necessary > to most people (like extra array operators). > > On Tue, 29 Aug 2000, Adam Lang wrote: > > > 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" <aalang@rutgersinsurance.com> writes:
> As for using 7.02, I had tried installing it from scratch, but had
> dependency problems. I attempted an "upgrade" just now, and here is the
> list of dependencies failures I had:
Seems like grabbing the source distribution and doing "configure; make;
make install" would be a lot easier ;-)
regards, tom lane
But wouldn't the dependencies be there whether I compile from scratch or use an RPM? What the main issue that stumped was the libc.6.so dependency. According to epm -a -q, it is installed. I guess I can blow out 6.5 and download the 7.0 source and reinstall a compiled version. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Adam Lang" <aalang@rutgersinsurance.com> Cc: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>; "PGSQL General" <pgsql-general@postgresql.org> Sent: Tuesday, August 29, 2000 6:47 PM Subject: Re: [GENERAL] foreign keys - script > "Adam Lang" <aalang@rutgersinsurance.com> writes: > > As for using 7.02, I had tried installing it from scratch, but had > > dependency problems. I attempted an "upgrade" just now, and here is the > > list of dependencies failures I had: > > Seems like grabbing the source distribution and doing "configure; make; > make install" would be a lot easier ;-) > > regards, tom lane
"Adam Lang" <aalang@rutgersinsurance.com> writes:
> But wouldn't the dependencies be there whether I compile from scratch or use
> an RPM?
But if you compile from source, it will use whatever libc you have
installed.
regards, tom lane