Microsoft KB Archive/277900

= PRB: Changes to Key Field in Parent Table Not Cascaded When Deleted Before Unlocking Record =

Article ID: 277900

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q277900



SYMPTOMS
When a key field in the parent table in a parent/child relationship is changed, the changes are not cascaded to the child table. If the record in the parent is deleted without first unlocking it, the corresponding records in the child table may be orphaned.



RESOLUTION
To work around this problem, unlock the record before you delete it. This causes the changes to be cascaded to the child table.



Steps to Reproduce Behavior
  Run the following code to create a database and tables with a parent/child relationship: SET EXCLUSIVE ON CREATE DATABASE testdata CREATE TABLE Table1 (PriKey C(10) PRIMARY KEY) CREATE TABLE Table2 (ChildKey C(10) PRIMARY KEY, Child N(4,0))

ALTER TABLE Table2 ADD FOREIGN KEY ChildKey TAG ChildKey REFERENCES Table1
 * Create Persistent Relationship.

INSERT INTO Table1 VALUES (&quot;Rec1&quot;) INSERT INTO Table1 VALUES (&quot;Rec2&quot;) INSERT INTO Table1 VALUES (&quot;Rec3&quot;) INSERT INTO Table1 VALUES (&quot;Rec4&quot;)
 * Add Records to the tables.

INSERT INTO Table2 VALUES (&quot;Rec1&quot;, 1) INSERT INTO Table2 VALUES (&quot;Rec1&quot;, 2) INSERT INTO Table2 VALUES (&quot;Rec1&quot;, 3) INSERT INTO Table2 VALUES (&quot;Rec2&quot;, 1) INSERT INTO Table2 VALUES (&quot;Rec2&quot;, 2) INSERT INTO Table2 VALUES (&quot;Rec2&quot;, 3) INSERT INTO Table2 VALUES (&quot;Rec3&quot;, 1) INSERT INTO Table2 VALUES (&quot;Rec3&quot;, 2) INSERT INTO Table2 VALUES (&quot;Rec3&quot;, 3) INSERT INTO Table2 VALUES (&quot;Rec4&quot;, 1) INSERT INTO Table2 VALUES (&quot;Rec4&quot;, 2) INSERT INTO Table2 VALUES (&quot;Rec4&quot;, 3) CLOSE ALL   Add Referential Integrity (RI) to the database. In the Command window, type the following: MODIFY DATABASE  From the Database menu, click Clean Up Database. From the Database menu, click Edit Referential Integrity. On the Rules for Updating tab, select Cascade. Do the same for the Rules for Deleting tab. On the Rules for Inserting tab, select Restrict. Click OK, and then click Yes to save changes and generate the RI code. Click Yes for the next message about generating RI code. Close the Database Designer.  Run the following code: USE table1 IN 0 SHARED ORDER TAG PriKey USE table2 IN 0 SHARED ORDER TAG ChildKey

SELECT table1 SET RELATION TO PriKey into Table2 </li> Browse both tables and select a record in Table1.</li>  Run the following code to change the primary key and delete a record in Table1: SELECT Table1 IF LOCK(STR(RECNO), &quot;Table1&quot;) OLDKEY = Table1.PriKey NEWKEY = TRIM(OLDKEY) + &quot;-BAK&quot;

* USING A SQL UPDATE STATEMENT, BUT REPLACE DOES THE SAME THING. UPDATE Table1 SET Table1.PriKey = NEWKEY WHERE Table1.PriKey = OLDKEY ** UNLOCK RECORD RECNO && uncomment to fix problem. DELETE UNLOCK RECORD RECNO && comment this line when uncommenting the && the line above. ENDIF </li> Note that the record you selected in Table1 is deleted and the PriKey field has &quot;-BAK on&quot; appended to it.</li> Close the Browse window for Table2. Make sure that Table2 is still selected in the current work area and then issue another BROWSE. The corresponding records in the child table remained unchanged.</li></ol>

<div class="references_section">