Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.4) with SMTP id f7UEtmf46045 for ; Thu, 30 Aug 2001 10:55:48 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from karloff.boston.amer.iona.com (boston.amer.iona.com [63.65.132.35] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7TLgbf22540 for ; Wed, 29 Aug 2001 17:42:37 -0400 (EDT) (envelope-from jayson.callaway@iona.com) Received: from jcallawa ([10.64.1.47]) by karloff.boston.amer.iona.com (8.9.3/iona-1.02-sjamison) with SMTP id RAA01473; Wed, 29 Aug 2001 17:41:08 -0400 (EDT) From: "Jayson Callaway" To: "Jason Earl" Cc: Subject: RE: changes to table creation syntax in 7.1.2? Date: Wed, 29 Aug 2001 17:42:51 -0400 Message-ID: <001c01c130d3$8da26f30$014ba8c0@boston.amer.iona.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2377.0 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 In-Reply-To: <20010829203447.73191.qmail@web10008.mail.yahoo.com> X-Archive-Number: 200108/366 X-Sequence-Number: 4621 Jason, First of all, thanks for your response. I had given up that anyone was going to respond. After seeing your response I went back and found what was making the difference. The failing syntax below worked on 7.0.3 but not on the 7.1.2 version. Seeing your working example(unlike another response) gave me the answer. Jayson This file of commands fail... [jayson@grendal db]$ cat test CREATE TABLE "test_cat" ( "uid" int4 NOT NULL, "name" text NOT NULL, "description" text NOT NULL, "parent_category_uid" int4 NULL REFERENCES test_cat(uid) ); CREATE UNIQUE INDEX test_cat_pkey on test_cat (uid); [jayson@grendal db]$ psql -e -f test -d wr -U wr_web CREATE TABLE "test_cat" ( "uid" int4 NOT NULL, "name" text NOT NULL, "description" text NOT NULL, "parent_category_uid" int4 NULL REFERENCES test_cat(uid) ); CREATE UNIQUE INDEX test_cat_pkey on test_cat (uid); psql:test:6: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) psql:test:6: ERROR: UNIQUE constraint matching given keys for referenced table "test_cat" not found [jayson@grendal db]$ While this set of commands is successsfull... [jayson@grendal db]$ cat test CREATE TABLE "test_cat" ( "uid" int4 PRIMARY KEY, "name" text NOT NULL, "description" text NOT NULL, "parent_category_uid" int4 NULL REFERENCES test_cat(uid) ); [jayson@grendal db]$ psql -e -f test -d wr -U wr_web CREATE TABLE "test_cat" ( "uid" int4 PRIMARY KEY, "name" text NOT NULL, "description" text NOT NULL, "parent_category_uid" int4 NULL REFERENCES test_cat(uid) ); psql:test:6: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_cat_pkey' for table 'test_cat' psql:test:6: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE [jayson@grendal db]$ > -----Original Message----- > From: Jason Earl [mailto:jdearl@yahoo.com] > Sent: Wednesday, August 29, 2001 4:35 PM > To: Jayson Callaway; pgsql-sql@postgresql.org > Subject: Re: [SQL] changes to table creation syntax in 7.1.2? > > > It looks like it works here :(. Do you have an error > message? > > processdata=# select version(); > version > > --------------------------------------------------------------- > PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by > GCC 2.95.4 > (1 row) > > processdata=# CREATE TABLE category > processdata-# ( > processdata(# uid int4 PRIMARY KEY, > processdata(# description text NOT NULL, > processdata(# parent int4 NULL REFERENCES > category(uid) > processdata(# ) > processdata-# > processdata-# ; > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit > index 'category_pkey' for table 'category' > NOTICE: CREATE TABLE will create implicit trigger(s) > for FOREIGN KEY check(s) > CREATE > > --- Jayson Callaway wrote: > > In postgres 7.0.x I had some working code that > > lookes something like: > > > > CREATE TABLE category > > ( > > uid int4 PRIMARY KEY, > > description text NOT NULL, > > parent int4 NULL REFERENCES category(uid) > > ) > > > > After upgrading to postgres 7.1.2 however this > > syntax is not accepted > > anymore. I receive an error that says it can not > > create the reference > > because the table category does not exist. > > > > How do I setup this type of reference structure in > > 7.1.2? Did the syntax > > change? > > > > I am running under Linux. > > -- > > Jayson Callaway > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > __________________________________________________ > Do You Yahoo!? > Get email alerts & NEW webcam video instant messaging with Yahoo! > Messenger > http://im.yahoo.com >