Re: failed SQL CREATE TABLE

-----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(at)visi(dot)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(at)postgresql(dot)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-----