Re: INSERT failing because of CONSTRAINT

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

I've got a similar problem for an app I'm writing as well. I'm new enough
to PostgreSQL that my solution may be backwards so take this with a grain
of salt.

I'm exporting my primary key to another table and using PL/pgSQL triggers
to keep it up to date. This is a simplified, syntactically invalid
example. The idea is that SoSPeople is a parent to a set of SoSPeople_##
tables with one per county in Minnesota (SoS is Secretary of State). Other
tables elsewhere in the application will have RI constraints against the
SoSIDs table while the SoSPeople heirarchy will maintain that table
through trigger functions. If I were fancy I could write triggers to
operate on the SoSPeople tables if the ID was altered/deleted in SoSIDs.
In my case that shouldn't ever happen so it's not coded for. This just
illustrates sticking your primary key out elsewhere. You will want to
expand on the code if you use it for real. I'll have the code to create
the app on http shortly and I could share it if you wanted to see a real
live example.

CREATE TABLE SoSIDs (
SoSID CHARACTER(10),
CONSTRAINT SoSIDsPKey PRIMARY KEY SoSID
);

CREATE TABLE SoSPeople(
Id INTEGER DEFAULT nextval('pid'::text) NOT NULL,
SoSID CHARACTER(10) NOT NULL,
CountyNumber INTEGER NOT NULL,
...
);
sospeople_1 ...
CREATE TABLE sospeople_27 () INHERITS (SoSPeople);
... sospeople_87

CREATE FUNCTION SoSPeopleIns() RETURNS OPAQUE AS '
BEGIN
-- this may have to EXECUTED instead of doing it directly
-- I was getting some error about a plain insert
INSERT INTO SoSIDs (SoSID) VALUES (NEW.SoSID);
RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE FUNCTION SoSPeopleUpd() RETURNS OPAQUE AS '
BEGIN
UPDATE SoSIDs SET SoSID = NEW.SoSID WHERE SoSID = OLD.SoSID;
RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE FUNCTION SoSPeopleDel() RETURNS OPAQUE AS '
BEGIN
DELETE FROM SoSIDs WHERE SoSID = OLD.SoSID;
RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

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

On Tue, 9 Apr 2002, Tim Wilson wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8tDyrfexLsowstzcRAo1XAKCTUR950JWrtRGExO0RvA/PIRnR8ACfc+c5
V46mwkkje0QCwkDl2/d5sS4=
=mDME
-----END PGP SIGNATURE-----