Microsoft KB Archive/175257

= PRB: Database Diagram Doesn't Restore Relationships After Cancel =

Article ID: 175257

Article Last Modified on 5/2/2006

-

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

 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.

<div class="references_section">