Re: failed SQL CREATE TABLE - Mailing list pgsql-novice

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd also question whether you can designate two columns as primary keys
separately. I rewrote the primary key to be a separate constraint. It's
more clear (to me anyway) that both keys are primary keys when used
together. And are you on TCLUG? Your name looks familiar.

Josh

CREATE TABLE event_participant (
   event_id INTEGER,
   participant_id INTEGER,
   CONSTRAINT event_pkey PRIMARY KEY(event_id,participant_id),
   CONSTRAINT event_exists FOREIGN KEY (event_id)
      REFERENCES event
      ON UPDATE CASCADE
      ON DELETE SET NULL,
   CONSTRAINT participant_exists FOREIGN KEY (participant_id)
      REFERENCES participant
      ON UPDATE CASCADE
      ON DELETE SET NULL
);


Joshua b. Jore
http://www.greentechnologist.org

On Mon, 8 Apr 2002, Tim Wilson wrote:

> Hi everyone,
>
> I'm trying to create a database and I'm getting some errors when I try
> to create some of the tables. I'm going to post the SQL and the errors
> and I'd appreciate it if there's a guru on the list who'd have a look
> and let me know where I'm messing up. Everything looks OK to me, but
> doesn't it always in situations like this. :-)
>
> Here's the SQL:
>
> CREATE TABLE location (
>   location_id SERIAL PRIMARY KEY,
>   room VARCHAR(15),
>   location_name VARCHAR(30),
>   address VARCHAR(30),
>   city VARCHAR(20),
>   state CHAR(2),
>   zip VARCHAR(10)
> );
> CREATE TABLE submitter (
>   username CHAR(10) PRIMARY KEY,
>   first_name VARCHAR(20),
>   last_name VARCHAR(30),
>   email VARCHAR(40)
> );
> CREATE TABLE participant (
>   participant_id SERIAL PRIMARY KEY,
>   org_name VARCHAR(40),
>   activity_name VARCHAR(40)
> );
> CREATE TABLE event (
>   event_id SERIAL PRIMARY KEY,
>   event_name VARCHAR(30),
>   location_id INTEGER,
>   start TIMESTAMP,
>   end TIMESTAMP,
>   description TEXT,
>   username CHAR(10),
>   CONSTRAINT location_exists FOREIGN KEY (location_id)
>     REFERENCES location
>     ON UPDATE CASCADE
>     ON DELETE SET NULL,
>   CONSTRAINT username_exists FOREIGN KEY (username)
>     REFERENCES submitter
>     ON UPDATE CASCADE
>     ON DELETE SET NULL
> );
> CREATE TABLE game (
>   our_score INTEGER,
>   their_score INTEGER)
>   INHERITS (event);
> CREATE TABLE tournament (
>   finish VARCHAR(15))
>   INHERITS (event);
> CREATE TABLE meeting (
>   agenda_url TEXT,
>   minutes_url TEXT)
>   INHERITS (event);
> And the errors I get:
>
> eventdb=# \i /home/wilson/make_eventdb.sql
> psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE will create
> implicit sequence 'location_location_id_seq' for SERIAL column
> 'location.location_id'
> psql:/home/wilson/make_eventdb.sql:9: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'location_pkey' for table 'location'
> CREATE
> psql:/home/wilson/make_eventdb.sql:15: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'submitter_pkey' for table 'submitter'
> CREATE
> psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE will create
> implicit sequence 'participant_participant_id_seq' for SERIAL column
> 'participant.participant_id'
> psql:/home/wilson/make_eventdb.sql:20: NOTICE:  CREATE TABLE/PRIMARY KEY
> will create implicit index 'participant_pkey' for table 'participant'
> CREATE
> psql:/home/wilson/make_eventdb.sql:37: ERROR:  parser: parse error at or
> near "end"
> psql:/home/wilson/make_eventdb.sql:41: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:44: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:48: ERROR:  Relation 'event' does not
> exist
> psql:/home/wilson/make_eventdb.sql:60: ERROR:  CREATE TABLE/PRIMARY KEY
> multiple primary keys for table 'event_participant' are not allowed
>
> Thanks again for any help anyone can offer.
>
> -Tim
>
> --
> Tim Wilson      |   Visit Sibley online:   | Check out:
> Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
> W. St. Paul, MN |                          | http://slashdot.org
> wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8sna+fexLsowstzcRAo4CAJoDPmlRS+rinWHnFaLbntQiG0MsBACcCrgJ
kxZ0MCfBOBZV8fTszGqfd7Q=
=0kBS
-----END PGP SIGNATURE-----