Re: foreign keys - script

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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005