Microsoft KB Archive/814724

= ORM Source Model Generates Incorrect DDL Code for Unique Constraint on Optional Columns =

Article ID: 814724

Article Last Modified on 5/31/2007

-

APPLIES TO


 * Microsoft Visio for Enterprise Architects 2002

-





SYMPTOMS
When you use Microsoft Visio for Enterprise Architects to generate the Data Definition Language (DDL) script for an Object Role Modeling (ORM) Source Model, and you have a unique constraint defined on a nullable column, you may receive the following warning message:

' : Primary key/unique constraint column is declared as nullable. Table can be created but you may not insert more than one null value into this column because it violates the constraint imposed by the DBMS.

In some databases, you cannot insert more than one null value in the unique constraint column if you create the table by using the generated DDL script.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



WORKAROUND
To work around this problem, you can use check constraints or triggers that have customized code to enforce uniqueness for a nullable column, instead of using unique constraints.

SQL-92 Code Examples for Check Constraints
  CHECK (UNIQUE (SELECT DeptHeaded FROM Employee WHERE DeptHeaded IS NOT NULL))   CHECK (NOT EXISTS (SELECT DeptHeaded FROM Employee WHERE DeptHeaded IS NOT NULL GROUP BY DeptHeaded HAVING COUNT(*) > 1) ) 

SQL Server Example for Trigger
CREATE TRIGGER Unique_DeptHeaded ON Employee AFTER INSERT, UPDATE AS   IF EXISTS (SELECT DeptHeaded FROM Employee                 WHERE DeptHeaded IS NOT NULL                 GROUP BY DeptHeaded                 HAVING count(*) > 1) BEGIN RAISERROR('Attempt was made to insert a duplicate DeptHeaded. The statement will be rolled back.',16,1) ROLLBACK END Note For the examples that appear in this article, assume that the tables do not have any duplicate data existing. You can also use these examples when you create the tables.



MORE INFORMATION
You can enforce uniqueness of the column through a unique constraint in the DDL script. Most database engines, including Microsoft SQL Server, do not permit duplicate null values to be inserted in a unique constraint column. Therefore, you may not be able to verify the uniqueness of only the non-null values in a column by using a unique constraint.

In most databases, including Microsoft SQL Server, you may not be able to define a table that has the Primary Key as a nullable column. Therefore, the warning message that is mentioned in the &quot;Symptoms&quot; section may not be appropriate.

Steps to Reproduce the Behavior
 Create the ORM Source Model. To do this, follow these steps:  Start Microsoft Visio for Enterprise Architects. On the File menu, point to New, point to Database, and then click ORM Source Model to create a new ORM Source Model.</li> In the Business Rules pane, click the Object Types tab.</li> Add the following objects. Use the RefMode values that appear in parenthesis after each object name: <ul>  Employee  ( RefMode: EmpNo )</li>  Department  ( RefMode: DeptCode )</li></ul>

Note Accept the default values for the properties of all other fields.</li> Click the Fact Types tab.</li> Press F2.</li> In the Fact Editor dialog box, click the Fact tab.</li> Add the following facts, and then click OK: <ul>  Employee works for Department </li>  Department is headed by Employee </li></ul> </li> Click the Constraints tab.</li> Based on the following information, add constraints for the facts that you defined in step 6, and then click OK: <ul> Each Employee works for exactly one Department</li> Each Employee heads at most one Department</li> Each Department is headed by at most one Employee</li></ul> </li> <li>Save the ORM Source Model.</li></ol> </li> <li>Create the Database Modeling project. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>On the File menu, point to New, point to Database, and then click Database Model Diagram to create a new Database Modeling project.</li> <li>On the Database menu, point to Project, and then click Add Existing Document.</li> <li>Locate the ORM Source Model that you created in the &quot;Create the Source ORM Model&quot; section, and then click Open.</li> <li>Save the Database Model Diagram.</li> <li>On the Database menu, point to Project, and then click Build to build the project.</li></ol> </li> <li>Customize the columns. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>In the Tables and Views explorer pane, click the table name.</li> <li>In Database Properties, click Definition in the Categories list box.</li> <li>Change the values in the Physical Name text box and the Conceptual Name text box to Employee .</li> <li>In Database Properties, click Columns in the Categories list box.</li> <li>Based on the following details, change the columns: <ul> <li>EmpNo (Required, Primary Key)</li> <li>DeptCode (Required)</li> <li>DeptHeaded (Not Required)</li></ul> </li></ol> </li> <li>On the Database menu, click Generate, and then follow the instructions in the Generate Wizard to generate the DDL script for the database model.</li> <li>Open the Output window to view the warning message that appears in the &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">