INSERT failing because of CONSTRAINT

Hi everyone,

Thanks to the folks who offered suggestions on my previous post. I've
got all that squared away, but now I've hit another snag as I'm trying
to insert some test data into my database.

If anyone is kind enough to offer advice on this one, it would probably
be best if you looked at the diagrams and table at
http://www.isd197.org/sibley/staff/wilsont/event_product/

I've created a junction table, one side of which refers to another table
that's acting as a parent to three child tables (using PostgreSQL's
inheritance). The junction table was created as follows:

CREATE TABLE event_participant (
event_id INTEGER PRIMARY KEY,
participant_id INTEGER PRIMARY KEY,
CONSTRAINT event_participant_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
);

You can see the second constraint references event which is the parent
table. The event table looks like this:

eventdb=# select * from event;
event_id | event_name | location_id | start_time |
end_time | description | username
----------+-------------+-------------+------------------------+----------+-------------+------------
1 | Tri-angular | 2 | 2002-11-04 13:30:00-06 |
|
| wilson
(1 row)

(Sorry about that wrapping.)

The data you see above was actually entered in the tournament table
which is a child of event. Now when I try to insert into my junction
table I get this:

eventdb=# select * from participant;
eventdb=# INSERT INTO event_participant (event_id, participant_id)
eventdb-# VALUES (1, 31);
ERROR: event_exists referential integrity violation - key referenced
from event_participant not found in event

Is there a way around this or should I just avoid the very cool
inheritance?

-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