DOCUMENT:Q194515  19-AUG-1999  [foxpro]
TITLE   :BUG: Invalid ALTER TABLE Command Works and Corrupts DBC
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:

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

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

SYMPTOMS
========

After issuing an invalid ALTER TABLE statement, running VALIDATE DATABASE fails
and returns an error.

RESOLUTION
==========

To avoid creating an invalid object through the ALTER TABLE command, be sure to
include an expression in the Add Foreign Key statement.

To remove the invalid object reference in the database, follow the steps listed
below:

1. Note the object number in the error below: it is Object #14.

2. Use the database as a table.

3. Delete the record for Object #14 (if that is the object number in the error)

4. Pack the database.

Fix Code For "Steps To Reproduce" Sample
----------------------------------------

         CLOSE DATABASE ALL
         USE testdb.DBC
         DELETE FOR objectid=14 &&Delete object #14's record
         PACK
         USE

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products listed at the
beginning of this article.

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

The ALTER TABLE command allows the entry of an Add Foreign Key statement without
specifying an expression. This causes an invalid entry in the database
referencing a relationship that does not exist. This causes the VALIDATE
DATABASE command to return the following error:

   "Object #14 (Relation 'Relation 1'): Cannot find associated Index referenced
   by this object"

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

1. Run the following code in a program file:

         #DEFINE DBCNAME    'testdb'
         #DEFINE TABNAME    'testtab'
         #DEFINE TABCOUNT      2

         CLEAR
         CLOSE DATA ALL
         DELETE FILE testdb.DBC
         DELETE FILE testdb.dc?

         CREATE DATABASE DBCNAME

         FOR lni = 1 TO TABCOUNT
            DELETE FILE (TABNAME+ALLTR(STR(lni)))+'.dbf'
            DELETE FILE (TABNAME+ALLTR(STR(lni)))+'.cdx'
            CREATE TABLE (TABNAME+ALLTR(STR(lni))) (F1 C(10) NOT NULL ;
               PRIMARY KEY, ;
               F2 C(10) NOT NULL)
         ENDFOR

         * Gives invalid DBC because Foreign key expression is missing
         ALTER TABLE (TABNAME+("2")) ADD FOREIGN KEY TAG ;
            F2 REFERENCES (TABNAME+"1") TAG F1

2. Issue the "VALIDATE DATABASE" command from the Command window. Note that this
   command returns the following error:

   "Object #14 (Relation 'Relation 1'): Cannot find associated Index referenced
   by this object"

REFERENCES
==========

For more information about the Alter Table command, search for "ALTER TABLE" in
the Visual FoxPro Help file.

For additional information, please see the following articles in the Microsoft
Knowledge Base:

   Q186242 BUG: ALTER TABLE Cannot Create a Descending Primary Key

   Q191697 BUG: ALTER TABLE Command With Multiple Drop Statements Fails

   Q191593 BUG: ALTER TABLE Causes Memo File is Missing or Invalid Err

Additional query words: kbVFP600bug kbDatabase kbDBFDBC

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Issue type        : kbbug

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

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