PRB: Database Diagram Doesn't Restore Relationships After Cancel (175257)


The information in this article applies to:

  • Microsoft Visual Database Tools
This article was previously published under Q175257

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/search/default.aspx?qu=vinterdev

Modification Type:MajorLast Reviewed:5/2/2006
Keywords:kbDatabase kbprb KB175257
©2004 Microsoft Corporation. All rights reserved.