Cascading Updates Fail for this situation

[Guest]

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

 

[Jamie Collins]

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'."

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?

So I didn't convince you that posting SQL DDL is the way to go, eh <g>?

The problem is that your hierarchy diverges after the tb_DO level then
converges again at the tb_Mod_Detail level, where the Base_ID,
Contract_Number and DO_Number columns (compound) reference two tables.

There is a solution to this problem (without a paradigm shift away from
natural keys - nice try, tina <g>) but it doesn't look too pretty.
Basically, duplicate the columns as necessary so that each is
referencing only one table. This will give you a nine column PK,
something like

Base_ID_in_tb_MOD,
Contract_Number_in_tb_MOD,
DO_Number_in_tb_MOD,
Mod_Number,
Base_ID_in_tb_Funding_Area,
Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area,
Funding_Area,
Project_Number

Here's the SQL DDL to recreate the problem and demo the proposed
solution:

Sub testcas()
Kill "C:\DropMe1.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe1.mdb"
With .ActiveConnection

.Execute _
"CREATE TABLE tb_Contract ( Base_ID INT NOT" & _
" NULL, Contract_Number INT NOT NULL, PRIMARY" & _
" KEY (Base_ID, Contract_Number));"

.Execute _
"CREATE TABLE tb_DO ( Base_ID INT NOT NULL," & _
" Contract_Number INT NOT NULL, DO_Number" & _
" INT NOT NULL, PRIMARY KEY (Base_ID, Contract_Number," & _
" DO_Number), FOREIGN KEY (Base_ID, Contract_Number)" & _
" REFERENCES tb_Contract (Base_ID, Contract_Number)" & _
" ON DELETE CASCADE ON UPDATE CASCADE );"

.Execute _
"CREATE TABLE tb_Mod ( Base_ID INT NOT NULL," & _
" Contract_Number INT NOT NULL, DO_Number" & _
" INT NOT NULL, Mod_Number INT NOT NULL," & _
" PRIMARY KEY (Base_ID, Contract_Number," & _
" DO_Number, Mod_Number), FOREIGN KEY (Base_ID," & _
" Contract_Number, DO_Number) REFERENCES" & _
" tb_DO (Base_ID, Contract_Number, DO_Number)" & _
" ON DELETE CASCADE ON UPDATE CASCADE );"

.Execute _
"CREATE TABLE tb_Funding_Area ( Base_ID INT" & _
" NOT NULL, Contract_Number INT NOT NULL," & _
" DO_Number INT NOT NULL, Funding_Area INT" & _
" NOT NULL, PRIMARY KEY (Base_ID, Contract_Number," & _
" DO_Number, Funding_Area), FOREIGN KEY (Base_ID," & _
" Contract_Number, DO_Number) REFERENCES" & _
" tb_DO (Base_ID, Contract_Number, DO_Number)" & _
" ON DELETE CASCADE ON UPDATE CASCADE );"

.Execute _
"CREATE TABLE tb_Mod_Detail ( Base_ID INT" & _
" NOT NULL, Contract_Number INT NOT NULL," & _
" DO_Number INT NOT NULL, Mod_Number INT" & _
" NOT NULL, Funding_Area INT NOT NULL, Project_Number" & _
" INT NOT NULL, PRIMARY KEY (Base_ID, Contract_Number," & _
" DO_Number, Mod_Number, Funding_Area, Project_Number)," & _
" FOREIGN KEY (Base_ID, Contract_Number," & _
" DO_Number, Funding_Area) REFERENCES tb_Funding_Area" & _
" (Base_ID, Contract_Number, DO_Number, Funding_Area)" & _
" ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN" & _
" KEY (Base_ID, Contract_Number, DO_Number," & _
" Mod_Number) REFERENCES tb_MOD (Base_ID," & _
" Contract_Number, DO_Number, Mod_Number)" & _
" ON DELETE CASCADE ON UPDATE CASCADE);"

.Execute _
"INSERT INTO tb_Contract (Base_ID, Contract_Number)" & _
" VALUES (1, 1);"

.Execute _
"INSERT INTO tb_DO (Base_ID, Contract_Number," & _
" DO_Number) VALUES (1, 1, 2);"

.Execute _
"INSERT INTO tb_Mod (Base_ID, Contract_Number," & _
" DO_Number, Mod_Number) VALUES (1, 1, 2," & _
" 3);"

.Execute _
"INSERT INTO tb_Funding_Area (Base_ID, Contract_Number," & _
" DO_Number, Funding_Area) VALUES (1, 1," & _
" 2, 4);"

.Execute _
"INSERT INTO tb_Mod_Detail (Base_ID, Contract_Number," & _
" DO_Number, Mod_Number, Funding_Area, Project_Number)" & _
" VALUES (1, 1, 2, 3, 4, 5);"

Dim rs
Set rs = .Execute( _
"SELECT Base_ID, Contract_Number, DO_Number," & _
" Mod_Number, Funding_Area, Project_Number" & _
" FROM tb_Mod_Detail;")
MsgBox rs.GetString
rs.Close

On Error Resume Next

' UPDATE and CASCADE will fail
.Execute _
"UPDATE tb_Contract SET Contract_Number =" & _
" 99, Base_ID = 55 WHERE Contract_Number" & _
" = 1 AND Base_ID = 1;"
If Err.Number <> 0 Then
MsgBox Err.Description
Else
Set rs = .Execute( _
"SELECT Base_ID, Contract_Number, DO_Number," & _
" Mod_Number, Funding_Area, Project_Number" & _
" FROM tb_Mod_Detail;")
MsgBox rs.GetString
rs.Close
End If
On Error GoTo 0

' Fix the schema
.Execute _
"DROP TABLE tb_Mod_Detail "

.Execute _
"CREATE TABLE tb_Mod_Detail ( Base_ID_in_tb_MOD" & _
" INT NOT NULL, Contract_Number_in_tb_MOD" & _
" INT NOT NULL, DO_Number_in_tb_MOD INT NOT" & _
" NULL, Mod_Number INT NOT NULL, Base_ID_in_tb_Funding_Area" & _
" INT NOT NULL, Contract_Number_in_tb_Funding_Area" & _
" INT NOT NULL, DO_Number_in_tb_Funding_Area" & _
" INT NOT NULL, Funding_Area INT NOT NULL," & _
" Project_Number INT NOT NULL, PRIMARY KEY" & _
" (Base_ID_in_tb_MOD, Contract_Number_in_tb_MOD," & _
" DO_Number_in_tb_MOD, Mod_Number, Base_ID_in_tb_Funding_Area," & _
" Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area," & _
" Funding_Area, Project_Number), FOREIGN" & _
" KEY (Base_ID_in_tb_Funding_Area,
Contract_Number_in_tb_Funding_Area," & _
" DO_Number_in_tb_Funding_Area, Funding_Area)" & _
" REFERENCES tb_Funding_Area (Base_ID, Contract_Number," & _
" DO_Number, Funding_Area) ON DELETE CASCADE" & _
" ON UPDATE CASCADE, FOREIGN KEY (Base_ID_in_tb_MOD," & _
" Contract_Number_in_tb_MOD, DO_Number_in_tb_MOD," & _
" Mod_Number) REFERENCES tb_MOD (Base_ID," & _
" Contract_Number, DO_Number, Mod_Number)" & _
" ON DELETE CASCADE ON UPDATE CASCADE);"

.Execute _
"INSERT INTO tb_Mod_Detail (Base_ID_in_tb_MOD," & _
" Contract_Number_in_tb_MOD, DO_Number_in_tb_MOD," & _
" Mod_Number, Base_ID_in_tb_Funding_Area," & _
" Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area," & _
" Funding_Area, Project_Number) VALUES (1," & _
" 1, 2, 3, 1, 1, 2, 4, 5);"

Set rs = .Execute( _
"SELECT Base_ID_in_tb_MOD, Contract_Number_in_tb_MOD," & _
" DO_Number_in_tb_MOD, Mod_Number, Base_ID_in_tb_Funding_Area," & _
" Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area," & _
" Funding_Area, Project_Number FROM tb_Mod_Detail;")
MsgBox rs.GetString
rs.Close

On Error Resume Next

' UPDATE and CASCADE will succeed
.Execute _
"UPDATE tb_Contract SET Contract_Number =" & _
" 99, Base_ID = 55 WHERE Contract_Number" & _
" = 1 AND Base_ID = 1;"
If Err.Number <> 0 Then
MsgBox Err.Description
Else
Set rs = .Execute( _
"SELECT Base_ID_in_tb_MOD, Contract_Number_in_tb_MOD," & _
" DO_Number_in_tb_MOD, Mod_Number, Base_ID_in_tb_Funding_Area," & _
" Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area," & _
" Funding_Area, Project_Number FROM tb_Mod_Detail;")
MsgBox rs.GetString
rs.Close
End If
On Error GoTo 0

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Jamie Collins]

Basically, duplicate the columns as necessary so that each is
referencing only one table. This will give you a nine column PK,
something like

Base_ID_in_tb_MOD,
Contract_Number_in_tb_MOD,
DO_Number_in_tb_MOD,
Mod_Number,
Base_ID_in_tb_Funding_Area,
Contract_Number_in_tb_Funding_Area,
DO_Number_in_tb_Funding_Area,
Funding_Area,
Project_Number

Here's the SQL DDL

I omitted the CHECK constraints to keep the duplicated columns in
synch:

CHECK (Base_ID_in_tb_MOD = Base_ID_in_tb_Funding_Area)
;
CHECK (Contract_Number_in_tb_MOD = Contract_Number_in_tb_Funding_Area)
;
CHECK (DO_Number_in_tb_MOD = DO_Number_in_tb_Funding_Area)
;

Jamie.

--