Cascading Updates Fail for this situation

[Bagger]

Let me explain my situation and tables real quick and then see if anyone can
suggest a better way to set this up.

tb_Contract has Primary Key fields: Base_ID, Contract_Number
tb_DO has Primary key fields: Base_ID, Contract_Number, DO_Number
tb_Mod has Primary key fields: Base_ID, Contract_Number, DO_Number, Mod_Number
tb_Funding_Area has primary key fields: Base_ID, Contract_Number, DO_Number,
Funding_Area
tb_Mod_Detail has primary key fields: Base_ID, Contract_Number, DO_Number,
Mod_Number, Funding_Area, Project_Number

Now, the relationships between them are all based on the fields that they
share. So the hierarchy goes:
tb_Contract (1-many) tb_DO (Base_ID, Contract_Number)
tb_DO (1-many) tb_Mod (Base_ID, Contract_Number, DO_Number)
tb_DO (1-many) tb_Funding_Area (Base_ID, Contract_Number, DO_Number)
tb_Mod (1-many) tb_Mod_Detail (Base_ID, Contract_Number, DO_Number,
Mod_Number)
tb_Funding_Area (1-many) tb_Mod_Detail (Base_ID, Contract_Number, DO_Number,
Funding_Area)

Ok, given that (I hope it was understandable , let me add that all of the
relationships have cascading updates and deletes set. The problem I'm having
is that when I try to update a contract_number in tb_Contract, the cascading
update fails with the error message:
"-2147467259 : Cannot perform cascading operation. There must be a related
record in table 'tb_mod'."

However, if I break the relationship between tb_Funding_Area and
tb_Mod_Detail, then the update works fine. The problem then is that I can't
cascade an update or deletion to the Funding_Area field in tb_Funding_Area

Is there some other way I should be setting this up? Is there a way to fix
the problem so that I don't have to break the relationship? Any help would
be greatly appreciated, thanks!

 

[tina]

the pros and cons of "natural" primary key (one or more unique-value fields
that occur naturally in a table) versus "surrogate" primary key fields (a
field - usually numeric, often Autonumber, data type - that is added to a
table solely to function as the table's primary key, and has no other value
in the table) have been debated many times in these newsgroups. there are
highly skilled, experienced developers on both sides of the debate, and i
have nothing enlightening to add to that body of discussion.

my personal preference and rule-of-thumb is: i don't use a multi-field key
in any table where the key will be used as a foreign key in a related child
table (all that repeating data just drives me up a wall). as a result, i use
mostly surrogate keys in my tables, rather than natural keys. and as a
result of that, i have no need to use the Cascade Update feature. (i *do*
use Cascade Delete in carefully selected relationships, but note that
**carefully selected** are the operative words here - the Cascade Delete
option should always be selected with extreme caution, after very careful
consideration of the ramifications.)
suggest you consider adding Autonumber fields to your tables to serve as the
primary keys; then each child table will have a one-field foreign key
linking it back to its' parent table, with no need for Cascade Update. if
you need to enforce *uniqueness* in a combination of other fields in a
table, you don't need to use those fields as part of a primary key -
instead, create a multi-field index (you can read up on the subject of table
indices in Help).

hth