Cascading Updates not working

G

Guest

I have my table relationships set up to do cascading updates and deletes, but
I have found that when I change a primary key value in the top-level table,
the update does not work and I get an error:

"Cannot perform cascading operation. There must be a related record in a
table 'tb_mod'."

I thought that the whole point of cascading updates is that it suspends
referential integrity rules long enough to update the keys in the necessary
tables. What am I doing wrong here? I'd like to explain my table structure
here, but I'm not sure how to do it in plain text. I do have cascading
updates and deletes set for each relationship down the table chain.
 
D

Duane Hookom

It sounds like you are attempting to change a foreign key value, not a
primary key value.
 
J

Jamie Collins

Bagger said:
the update does not work and I get an error:

"Cannot perform cascading operation. There must be a related record in a
table 'tb_mod'."
Click to expand...

Is that verbatim? I don't see it on the list of related messages:

http://office.microsoft.com/en-us/assistance/HA011739511033.aspx

The above article may help you resolve the issue. If not, post back
with some more details.
I'd like to explain my table structure
here, but I'm not sure how to do it in plain text.
Click to expand...

A good way to do this, and is considered netiquette in most (all?)
other SQL DBMS-related newsgroups, is to post SQL DLL statements (e.g.
CREATE TABLE) to create the structure and SQL DML statements (e.g.
INSERT INTO) to create test data. It is not popular in the Access
newsgroups for various reasons: not everyone speaks SQL in the first
place; not everyone creates their table structures using SQL DDL; there
is no built-in tool for retrospectively creating a SQL script that
would recreate an existing structure; Access/Jet can only execute one
SQL statement at a time i.e. even if you gave someone a script there is
not build-in tool to execute it.

Recently I have been posting my 'scripts' as a simple VBA sub procedure
which uses late binding (e.g. no references) to create a new mdb then
execute the SQL statements and show simple results in a messagebox. In
other words, it's a simple copy+paste job. I've received no feedback on
this so maybe this approach isn't appreciated either (sob, sob).

Here's an example (not that for DRI I explicitly declare NO ACTION if
I'm not using a CASCADE action):

Sub SobStory()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Pilots (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT pk__Pilots__pilot_ID" & _
" PRIMARY KEY, CONSTRAINT pilot_ID_pattern" & _
" CHECK (pilot_ID NOT LIKE '%[!0-9]%' AND" & _
" pilot_ID NOT LIKE '*[!0-9]*'), last_name" & _
" VARCHAR(35) NOT NULL, first_name VARCHAR(35)" & _
" NOT NULL, middle_name VARCHAR(35) DEFAULT" & _
" '{{NA}}' NOT NULL);"

.Execute _
"CREATE TABLE Earnings (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT fk__Earnings__pilot_ID" & _
" REFERENCES Pilots (pilot_ID) ON DELETE" & _
" CASCADE ON UPDATE NO ACTION, earnings_amount" & _
" DECIMAL(19,4) DEFAULT 0 NOT NULL, CONSTRAINT" & _
" earnings_amount__must_be_positive CHECK" & _
" (earnings_amount >= 0));"

.Execute _
"INSERT INTO Pilots (pilot_ID, last_name," & _
" first_name) VALUES ('1234567890', 'A'," & _
" 'A');"

.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('1234567890', 20.00);"

' This will fail
.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('9999999999', 20.00);"

' This will fail
.Execute _
"UPDATE Pilots SET pilot_ID = '9876543210'" & _
" WHERE pilot_ID = '1234567890';"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
G

Guest

The error message is verbatim. I didn't see it on that page either. It
occurs when trying to update a record in the top-level table. I think I have
a suspect relationship that I need to change or remove that may be causing
the problem. Basically, I have one table in the chain that has a one-to-many
relationship to two more tables, which in turn each have a one-to-many with
another table down the chain. The foreign keys from the two tables are both
used as part of the primary key of the third table. Seems like this is
probably what's causing a problem with the cascading update. I'm going to
experiment a bit with it today.

I'd love to have a tool that would generate and execute scripts for creating
databases in Access. That would be fantastic. It's probably possible to
create a module that would do that, but I definitely don't have time to write
such a thing at the moment.

Jamie Collins said:
the update does not work and I get an error:

"Cannot perform cascading operation. There must be a related record in a
table 'tb_mod'."
Click to expand...

Is that verbatim? I don't see it on the list of related messages:

http://office.microsoft.com/en-us/assistance/HA011739511033.aspx

The above article may help you resolve the issue. If not, post back
with some more details.
I'd like to explain my table structure
here, but I'm not sure how to do it in plain text.
Click to expand...

A good way to do this, and is considered netiquette in most (all?)
other SQL DBMS-related newsgroups, is to post SQL DLL statements (e.g.
CREATE TABLE) to create the structure and SQL DML statements (e.g.
INSERT INTO) to create test data. It is not popular in the Access
newsgroups for various reasons: not everyone speaks SQL in the first
place; not everyone creates their table structures using SQL DDL; there
is no built-in tool for retrospectively creating a SQL script that
would recreate an existing structure; Access/Jet can only execute one
SQL statement at a time i.e. even if you gave someone a script there is
not build-in tool to execute it.

Recently I have been posting my 'scripts' as a simple VBA sub procedure
which uses late binding (e.g. no references) to create a new mdb then
execute the SQL statements and show simple results in a messagebox. In
other words, it's a simple copy+paste job. I've received no feedback on
this so maybe this approach isn't appreciated either (sob, sob).

Here's an example (not that for DRI I explicitly declare NO ACTION if
I'm not using a CASCADE action):

Sub SobStory()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Pilots (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT pk__Pilots__pilot_ID" & _
" PRIMARY KEY, CONSTRAINT pilot_ID_pattern" & _
" CHECK (pilot_ID NOT LIKE '%[!0-9]%' AND" & _
" pilot_ID NOT LIKE '*[!0-9]*'), last_name" & _
" VARCHAR(35) NOT NULL, first_name VARCHAR(35)" & _
" NOT NULL, middle_name VARCHAR(35) DEFAULT" & _
" '{{NA}}' NOT NULL);"

.Execute _
"CREATE TABLE Earnings (pilot_ID CHAR(10)" & _
" NOT NULL CONSTRAINT fk__Earnings__pilot_ID" & _
" REFERENCES Pilots (pilot_ID) ON DELETE" & _
" CASCADE ON UPDATE NO ACTION, earnings_amount" & _
" DECIMAL(19,4) DEFAULT 0 NOT NULL, CONSTRAINT" & _
" earnings_amount__must_be_positive CHECK" & _
" (earnings_amount >= 0));"

.Execute _
"INSERT INTO Pilots (pilot_ID, last_name," & _
" first_name) VALUES ('1234567890', 'A'," & _
" 'A');"

.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('1234567890', 20.00);"

' This will fail
.Execute _
"INSERT INTO Earnings (pilot_ID, earnings_amount)" & _
" VALUES ('9999999999', 20.00);"

' This will fail
.Execute _
"UPDATE Pilots SET pilot_ID = '9876543210'" & _
" WHERE pilot_ID = '1234567890';"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.
Click to expand...
 
J

Jamie Collins

Bagger said:
The error message is verbatim.
It occurs when trying to update a record in the top-level table.

Basically, I have one table in the chain that has a one-to-many
relationship to two more tables, which in turn each have a one-to-many with
another table down the chain. The foreign keys from the two tables are both
used as part of the primary key of the third table.

I'd love to have a tool that would generate and execute scripts for creating
databases in Access.
Click to expand...

Well, I think I follow your 'plain text' description OK <g>.

I've reproduced the message and can confirm it's generated by the Jet
engine (the page I previously linked to was specific to Access).

There is a discussion along these lines in another of the Access
groups:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/57f89bdcace2e3e4/

One approach would seem to be to 1) temporarily suspend (i.e. DROP to
later re-CREATE) the FKs while you, in a transaction or a backup,
change the values 'by hand' and rollback if the FKs cannot be
reapplied. Another suggestion is to change the design of the physical
implementation, if not the logical model, to avoid the situation (bear
in mind the person suggesting this couldn't see your situation arising
in the first place <g>).

Here's my code to reproduce:

Sub MultipleUpdatePaths()
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 Level1 (key_col_level1 INTEGER" & _
" NOT NULL PRIMARY KEY);"

.Execute _
"CREATE TABLE Level2a (key_col_level1 INTEGER" & _
" NOT NULL CONSTRAINT fk__Level2a__Level1" & _
" REFERENCES Level1 (key_col_level1) ON DELETE" & _
" CASCADE ON UPDATE CASCADE, key_col_level2a" & _
" INTEGER NOT NULL, PRIMARY KEY (" & _
" key_col_level1, key_col_level2a));"

.Execute _
"CREATE TABLE Level2b ( key_col_level1 INTEGER" & _
" NOT NULL CONSTRAINT fk__Level2b__Level1" & _
" REFERENCES Level1 (key_col_level1) ON DELETE" & _
" CASCADE ON UPDATE CASCADE, key_col_level2b" & _
" INTEGER NOT NULL, PRIMARY KEY (" & _
" key_col_level1, key_col_level2b));"

.Execute _
"CREATE TABLE Level3 ( key_col_level1 INTEGER" & _
" NOT NULL, key_col_level2a INTEGER NOT NULL," & _
" key_col_level2b INTEGER NOT NULL, key_col_level3" & _
" INTEGER, PRIMARY KEY (key_col_level3," & _
" key_col_level2b, key_col_level2a, key_col_level1)," & _
" CONSTRAINT fk__Level3__Level2a FOREIGN" & _
" KEY (key_col_level1, key_col_level2a) REFERENCES" & _
" Level2a (key_col_level1, key_col_level2a)" & _
" ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT" & _
" fk__Level3__Level2b FOREIGN KEY (key_col_level1," & _
" key_col_level2b) REFERENCES Level2b (key_col_level1," & _
" key_col_level2b) ON DELETE CASCADE ON UPDATE" & _
" CASCADE);"

.Execute _
"INSERT INTO Level1 (key_col_level1)" & _
" VALUES (1);"

.Execute _
"INSERT INTO Level2a (key_col_level1, key_col_level2a)" & _
" VALUES (1, 1);"

.Execute _
"INSERT INTO Level2b (key_col_level1, key_col_level2b)" & _
" VALUES (1, 1);"

.Execute _
"INSERT INTO Level3 (key_col_level1, key_col_level2a," & _
" key_col_level2b, key_col_level3)" & _
" VALUES (1, 1, 1, 1);"

' This will fail:
.Execute _
"UPDATE Level1" & _
" SET key_col_level1 = 99" & _
" WHERE key_col_level1 = 1;"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascading Updates Fail for this situation 3
Linked SQL Tables don't report Cascading Updates and Deletes 2
Relationship Question 6
Access not enforcing referential integrity!!! 11
Maintaining Referential Integrity 1
Problem with Refrential Integrity 5
Can't join 3
Referential Integrity for Lookup fields in tables 7

Top