DOCUMENT:Q277900  22-NOV-2000  [foxpro]
TITLE   :PRB: Changes to Key Field in Parent Table Not Cascaded to Child
PRODUCT :Microsoft FoxPro
PROD/VER::5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbDatabase kbvfp500 kbvfp500a kbvfp600 kbGrpDSFox kbDSupport kbCodeSnippet

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a, 6.0 
-------------------------------------------------------------------------------

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.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. 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))

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

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

   INSERT INTO Table2 VALUES ("Rec1", 1)
   INSERT INTO Table2 VALUES ("Rec1", 2)
   INSERT INTO Table2 VALUES ("Rec1", 3)
   INSERT INTO Table2 VALUES ("Rec2", 1)
   INSERT INTO Table2 VALUES ("Rec2", 2)
   INSERT INTO Table2 VALUES ("Rec2", 3)
   INSERT INTO Table2 VALUES ("Rec3", 1)
   INSERT INTO Table2 VALUES ("Rec3", 2)
   INSERT INTO Table2 VALUES ("Rec3", 3)
   INSERT INTO Table2 VALUES ("Rec4", 1)
   INSERT INTO Table2 VALUES ("Rec4", 2)
   INSERT INTO Table2 VALUES ("Rec4", 3)
   CLOSE ALL

2. Add Referential Integrity (RI) to the database. In the Command window, type
   the following:

    MODIFY DATABASE

3. From the Database menu, click Clean Up Database.

4. 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.

5. Close the Database Designer.

6. 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

7. Browse both tables and select a record in Table1.

8. Run the following code to change the primary key and delete a record in
   Table1:

   SELECT Table1
   IF LOCK(STR(RECNO()), "Table1")
      OLDKEY = Table1.PriKey
      NEWKEY = TRIM(OLDKEY) + "-BAK"

      * 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

9. Note that the record you selected in Table1 is deleted and the PriKey field
   has "-BAK on" appended to it.

10. 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.

REFERENCES
==========

(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Mark
Barnard, Microsoft Corporation.


Additional query words:

======================================================================
Keywords          : kbDatabase kbvfp500 kbvfp500a kbvfp600 kbGrpDSFox kbDSupport kbCodeSnippet 
Technology        : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP600 kbVFP500a
Version           : :5.0,5.0a,6.0
Issue type        : kbprb
Solution Type     : kbpending

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.