PRB: Database Diagram Does Not Restore Relationships After Cance

Last reviewed: December 11, 1997
Article ID: Q175257
The information in this article applies to:
  • Microsoft Visual Database Tools, version 1.0

SYMPTOMS

If you cancelled saving a Database Diagram that involved multiple table edits after a previous attempt to save, you might discover that relationships to tables no longer exist the next time you open your diagram.

CAUSE

The Database Designer groups all the DDL changes required for tables into separate transactions. In many cases, the Database Designer deletes foreign key relationships that point to a table before it can re-create it in a separate transaction. Should the commit action fail on one of the related tables, the database state will be inconsistent with what is shown in the diagram. If you then close the diagram without saving, you'll lose your relationships.

RESOLUTION

If you encounter an error during a diagram save action, you should use the Query Designer to fix your data or go back into your diagram and remove any constraints that might prevent you from saving the diagram. Once you've corrected the problem, save your diagram changes and the relationships will be restored correctly.

STATUS

This behavior is by design.

MORE INFORMATION

For more information regarding how to resolve commit failures when saving diagrams, see the Help topic "Troubleshooting Errors" under Visual Database Tools/Database Designer/Troubleshooting.

Steps to Reproduce Behavior

The following example uses the pubs database that ships with SQL Server.

  1. Create a new Database Diagram and add the stores, sales, and discounts tables to the diagram. Select all the tables, and select Table from the View menu, then click the Column Properties command.

  2. Change the datatype for stor_id in the discounts table to id (varchar) and accept the Datatype Change Required dialog box. Check off Allow Nulls for the stor_id column in the discounts table.

  3. Save diagram, click yes on the Save dialog box and you get a save incomplete dialog box with the following ODBC error:

          [Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to insert the
          value NULL into column 'stor_id', table 'pubs.dbo.Tmp_discounts_8';
          column does not allow nulls. INSERT fails.
    

  4. Click OK and close the diagram, say no to save changes reopen the saved diagram. The relation between the stores and discounts table is gone.

The correct way of handling the ODBC error was to either click back ON Allow Nulls for the stor_id column in the discounts table and then save the diagram. The Database Designer would have correctly restored the relationship between discounts and stores tables.

REFERENCES

Visual InterDev Help: search on "Troubleshooting Errors" under Visual Database Tools/Database Designer/Troubleshooting.

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

   http://support.microsoft.com/support/vinterdev/

Keywords          : kbDtDbDesigner kbDtQDesigner VIDataTools
Technology        : kbInetDev
Version           : WINDOWS:1.0
Platform          : WINDOWS
Issue type        : kbprb


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


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.

Last reviewed: December 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.