Microsoft KB Archive/811589

= BUG: The Generate Wizard in Microsoft Visio does not create the &quot;Unique index with constraint on top&quot; index in a SQL Server database =

Article ID: 811589

Article Last Modified on 1/28/2004

-

APPLIES TO


 * Microsoft Visio for Enterprise Architects 2002
 * Microsoft SQL Server 2000 Standard Edition

-





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: : Alternate key on table   and alternate key   (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:
 * 6) * For the Index name property, set the value to index1.
 * 7) * For the Index type property, set the value to Unique index with constraint on top.
 * 8) Click the index1 index, and then click Rename.
 * 9) Type different names in the Index name box and in the Unique constraint name box, and then click OK.
 * 10) 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.



Steps to Reproduce the Problem
 Start Microsoft Visio. On the File menu, point to New, point to Database, and then click Database Model Diagram. In the left pane, under Entity Relationship, drag an Entity shape to the drawing page. 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. Create a new index on the second column (col2), and then set the following properties: <ul> For the Index name property, set the value to index1.</li> For the Index type property, set the value to Unique index with constraint on top.</li></ul> </li> On the Database menu, click Generate.</li> In the Generate Wizard dialog box, click to select the Generate New Database check box, and then click Next.</li> Click Microsoft SQL Server in the Installed Visio drivers list, and then click Database already exists.</li> Click Next.</li> Click New, and then follow the instructions to create a new system data source.</li> Click the newly created data source, and then click Next.</li> In the Connect Data Source dialog box, type the user name and password to connect to SQL Server, and then click OK.</li> 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.

</li> 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 &quot;Table1&quot; ( &quot;col1&quot; char(10) not null, &quot;col2&quot; char(10) not null)

alter table &quot;Table1&quot; add constraint &quot;Table1_PK&quot; primary key (&quot;col1&quot;)

create unique index &quot;index1&quot; on &quot;Table1&quot; ( &quot;col2&quot;)

alter table &quot;Table1&quot; add constraint &quot;index1&quot; unique ( &quot;col2&quot;)

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.</li> On the Database menu, point to Model, and then click Error Check.

The message that is listed in the &quot;Symptoms&quot; section of this article may appear in the Output pane.</li></ol>

Additional query words: Generate database

Keywords: kberrmsg kbbug kbdatabase KB811589

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.