BUG: The Generate Wizard in Microsoft Visio does not create the "Unique index with constraint on top" index in a SQL Server database (811589)


The information in this article applies to:

  • Microsoft Visio for Enterprise Architects (2002)
  • Microsoft SQL Server 2000 (all editions)

SYMPTOMS

In Microsoft Visio, you create a database model diagram that has an Entity shape and the diagram contains a Unique index with constraint on top index. When you try use the Generate Wizard to generate a Microsoft SQL Server 2000 database for the database model diagram, SQL Server does not create the index successfully.

Additionally, if you validate your database model diagram after you generate the database, you may receive the following message during the logical validation of the database model diagram:
warning L4005: Index Name: Alternate key on table Table Name and alternate key Index Name (on same table) are functionally identical.

CAUSE

This problem occurs because SQL Server does not support Unique index with constraint on top indexes. Therefore, when you try to generate the database for your database model diagram, the index is divided into two parts. The first part is the unique index, and the second part is the unique constraint. However, the unique index part and the unique constraint part have the same name. Therefore, SQL Server does not create the unique constraint successfully because it already created a unique index that has the same name.

WORKAROUND

To work around this problem, provide different names for the unique index and the unique constraint of the Unique index with constraint on top index, and then generate the database for the database model diagram. To do so, follow these steps:
  1. Start Microsoft Visio.
  2. On the File menu, point to New, point to Database, and then click Database Model Diagram.
  3. In the left pane, under Entity Relationship, drag an Entity shape to the drawing page.
  4. Add two columns (col1 and col2) to the Entity shape, and then make sure that the first column (col1) is defined as a Primary Key.
  5. Create a new index on the second column (col2), and then set the following properties:
    • For the Index name property, set the value to index1.
    • For the Index type property, set the value to Unique index with constraint on top.
  6. Click the index1 index, and then click Rename.
  7. Type different names in the Index name box and in the Unique constraint name box, and then click OK.
  8. On the Database menu, click Generate, and then follow the instructions in the Generate Wizard to generate the SQL Server database.

STATUS

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

MORE INFORMATION

Steps to Reproduce the Problem

  1. Start Microsoft Visio.
  2. On the File menu, point to New, point to Database, and then click Database Model Diagram.
  3. In the left pane, under Entity Relationship, drag an Entity shape to the drawing page.
  4. Add two columns (col1 and col2) to the Entity shape, and then make sure that the first column (col1) is defined as a Primary Key.
  5. Create a new index on the second column (col2), and then set the following properties:
    • For the Index name property, set the value to index1.
    • For the Index type property, set the value to Unique index with constraint on top.
  6. On the Database menu, click Generate.
  7. In the Generate Wizard dialog box, click to select the Generate New Database check box, and then click Next.
  8. Click Microsoft SQL Server in the Installed Visio drivers list, and then click Database already exists.
  9. Click Next.
  10. Click New, and then follow the instructions to create a new system data source.
  11. Click the newly created data source, and then click Next.
  12. In the Connect Data Source dialog box, type the user name and password to connect to SQL Server, and then click OK.
  13. Click Next.

    You may receive the following message:
    P1710: 'Table1.col2': 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.
  14. Click Finish.

    The following messages are generated in the Output pane while the database for the database model diagram is generated:
    Started generating the database ...
    create table "Table1" ( "col1" char(10) not null, "col2" char(10) not null)
    alter table "Table1" add constraint "Table1_PK" primary key ("col1")
    create unique index "index1" on "Table1" ( "col2")
    alter table "Table1" add constraint "index1" unique ( "col2")
    There is already an index on table 'Table1' named 'index1'.
    Completed generating the database.
    Extracting/Refetching extended attribute for table 'Table1'.
    Extracting/Refetching extended attribute for key Table17.inedx1'.


    Note SQL Server creates a unique index that is named index1. However, SQL Server does not create the unique constraint that is also named index1.
  15. On the Database menu, point to Model, and then click Error Check.

    The message that is listed in the "Symptoms" section of this article may appear in the Output pane.
Modification Type:MajorLast Reviewed:1/28/2004
Keywords:kbDatabase kberrmsg kbBug KB811589 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.