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