Re: Referential Integrity problem...

On Thu, 30 Aug 2001, Justin wrote:

(cc'd to PG list in hopes that someone else might find this useful,
or that someone else might have a better solution.)

> Hi Joel,
>
> Have you ever come across the problem of having multiple fields in a
> table referring to one key field in another table?
>
> i.e.
> table job (level integer, descript varchar(20))
> table area_access (person_name varchar(40), left_wing integer references
> job(level) on update cascade, right_wing integer references job(level)
> on update cascade, foyer integer references job(level) on update
> cascade)
>
> The problem with this is that whenever one of the fields in job.level is
> updated, the RI trigger fires and tries to update the first field in
> table area_access, the second field is now in violation of the RI
> constraint though, so it errors out and rolls the transaction back.
> Creating the table with DEFERRABLE and INITIALLY DEFERRED however
> doesn't help, the RI triggers don't seem to fire until COMMIT is done,
> at which time the second field to be checked violates the RI constraint
> and the transaction is still rolled back. :(
>
> Any ideas?

I've had this problem w/other DBs, but not w/PG. I'm not sure if this
behavior is meant to be bug-for-bug compatible w/other products, or if
(even worse) the standards demand it. Might want to send you post to
hackers and see if they have any ideas on better fixes.

Three possible solutions present themselves; none are perfect.

I:

The mean database guy in me would first ask if you could successfully
normalize the area_access table to something like

person_name varchar(40)
area char(10) references areas
job integer references job(level) ...
primary key ( person_name, area )

[area table holds values 'left_wing', 'right_wing', 'foyer']

This also helps, of course, if you add new areas besides the two wings and
the foyer.

(If your concern is that its ugly to get data into a table like this, one
very nice trick w/PG is using tables w/RULES. You can make an empty table
like your original area_access, and have rules that rip it apart on INSERT
or UPDATE and correct the "real" normalized table.)

II:

Handle the ref int yourself w/triggers. Ick, ick, ick.

III:

Have three job tables. All INSERTs, UPDATEs, DELETEs to the first roll out
to the second and third, so they're always synchronized. Each field in
area_access is ref'd against a different table.

Based on your tolerance for kludges, and the real setup of your tables,
you'll have to pick.

HTH. Take care,
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington