ORM Source Model Generates Incorrect DDL Code for Unique Constraint on Optional Columns (814724)

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:

Full path of project file : warning P1710: 'Table Name.Column Name' : 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.

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 "Symptoms" section may not be appropriate.

Steps to Reproduce the Behavior

Create the ORM Source Model. To do this, follow these steps:
1. Start Microsoft Visio for Enterprise Architects.
2. On the File menu, point to New, point to Database, and then click ORM Source Model to create a new ORM Source Model.
3. In the Business Rules pane, click the Object Types tab.
4. Add the following objects. Use the RefMode values that appear in parenthesis after each object name:
  - Employee ( RefMode: EmpNo)
  - Department ( RefMode: DeptCode)
  Note Accept the default values for the properties of all other fields.
5. Click the Fact Types tab.
6. Press F2.
7. In the Fact Editor dialog box, click the Fact tab.
8. Add the following facts, and then click OK:
  - Employee works for Department
  - Department is headed by Employee
9. Click the Constraints tab.
10. Based on the following information, add constraints for the facts that you defined in step 6, and then click OK:
  - Each Employee works for exactly one Department
  - Each Employee heads at most one Department
  - Each Department is headed by at most one Employee
11. Save the ORM Source Model.
Create the Database Modeling project. To do this, follow these steps:
1. On the File menu, point to New, point to Database, and then click Database Model Diagram to create a new Database Modeling project.
2. On the Database menu, point to Project, and then click Add Existing Document.
3. Locate the ORM Source Model that you created in the "Create the Source ORM Model" section, and then click Open.
4. Save the Database Model Diagram.
5. On the Database menu, point to Project, and then click Build to build the project.
Customize the columns. To do this, follow these steps:
1. In the Tables and Views explorer pane, click the table name.
2. In Database Properties, click Definition in the Categories list box.
3. Change the values in the Physical Name text box and the Conceptual Name text box to Employee.
4. In Database Properties, click Columns in the Categories list box.
5. Based on the following details, change the columns:
  - EmpNo (Required, Primary Key)
  - DeptCode (Required)
  - DeptHeaded (Not Required)
On the Database menu, click Generate, and then follow the instructions in the Generate Wizard to generate the DDL script for the database model.
Open the Output window to view the warning message that appears in the "Symptoms" section.