By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,782 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,782 IT Pros & Developers. It's quick & easy.

trigger problem where two tables reference each other's primary keys

P: n/a
Table "grps"
Column | Type | Modifiers
-------------+-----------------------------+--------------------------------
---------------------
grpsid | integer | not null default
nextval('"grps_grpsid_seq"'::text)
grpsname | text | not null
memberofgrp | integer | not null default -1
dateadded | timestamp without time zone | not null default 'now'
addedby | integer | not null default 0
dateupdated | timestamp without time zone | not null default 'now'
updatedby | integer | not null default 0
outsidegrp | boolean | not null default 'f'::bool
active | boolean | not null default 't'::bool
notes | text |

Table "usrs"
Column | Type |
Modifiers
--------------------+-----------------------------+-------------------------
----------------------------
usrsid | integer | not null default
nextval('"usrs_usrsid_seq"'::text)
reportsto | integer | not null default -1
grpsid | integer | not null default -1
titlesid | integer | not null default 0
departmentsid | integer | not null default 0
firstname | character varying(100) |
lastname | character varying(100) |
phone | character varying(25) |
fax | character varying(25) |
celphone | character varying(25) |
address1 | character varying(100) |
address2 | character varying(100) |
citiesid | integer | not null default 0
states_provincesid | integer | not null default 0
postalcodeid | integer | not null default 0
countriesid | integer | not null default 0
email | character varying(255) |
password | character varying(255) | not null default '!'
revealcel | boolean | not null default
'f'::bool
revealemail | boolean | not null default
'f'::bool
outsideusr | boolean | not null default
't'::bool
dateadded | timestamp without time zone | not null default 'now'
addedby | integer | not null default 0
dateupdated | timestamp without time zone | not null default 'now'
updatedby | integer | not null default 0
active | boolean | not null default
't'::bool
notes | text |
in the usrs table, addedby and updatedby are self-referential to usrsid
grpsid refers to grpsid in grps

in the grps table, addedby and updatedby refer to the usrs table.

deletes trigger set default and updates cascade

when I update the usrs table ( any other table that has reference to the
usrsid column of usrs) no problem. when I update the grps table in any way,
I get

update grps set memberofgrp = 7 where grpsid = 6;
ERROR: constraint updgrpsupdatedbyusrsusrsid: table usrs does not have an
attribute usrsid

here is that constraint, generated by postgres at table creation and named
later:
CREATE CONSTRAINT TRIGGER "updgrpsupdatedbyusrsusrsid" AFTER UPDATE ON
"grps" FROM "usrs" DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_cascade_upd" ('updgrpsupdatedbyusrsusrsid', 'grps',
'usrs', 'UNSPECIFIED', 'updatedby', 'usrsid');

The web application that this serves as backend for can't be updating grps
unless a valide usrsid logged in, so I can be confident that the usrsid of
the updating/adding person will be in the usrs table, but I was hoping to
keep the referential integrity for consistency with the rest of the database
(and because I see it as a Good Thing) any ideas on the source of the bogus
error? Is it the cross-linking between the tables what is setting it off? Is
there a way to compensate for that?
Thanks,

Sean
Web Solutions That Work Developing custom web solutions designed
specifically to accomplish the unique objectives of our clients. Phone
503-639-2727 Fax 503-639-0807
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.