Microsoft KB Archive/153224

= BUG: Err Msg 549 When Adding Foreign Key on Primary Key Column =

Article ID: 153224

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q153224



BUG# 13578 (6.0, 6.50) (sqlserver)



SYMPTOMS
Adding a foreign key constraint on a primary key column using ALTER TABLE can fail with the following error:

Msg 549, Level 16, State 2: data exists in table ' ', database ' ', that violates FOREIGN KEY constraint ' ' being added ALTER command has been aborted



CAUSE
This problem occurs when the child table has a multicolumn primary key already defined and both primary keys for the child and parent tables use non-clustered indexes.

The problem does not occur if:


 * 1) The foreign key constraint was created using CREATE TABLE.
 * 2) The foreign key column was not part of a primary key.
 * 3) Both parent and child tables have primary keys that use clustered indexes.



WORKAROUND
Any of the following will allow you to work around the problem:


 * 1) Create the foreign key constraint using CREATE TABLE.
 * 2) Drop the existing primary key constraint on the child table, alter the table to create the foreign key constraint, and then recreate the primary key constraint.
 * 3) Use clustered indexes for primary key of parent and child table.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0 and Microsoft SQL Server 6.5. This problem does not occur in Microsoft SQL Server 6.5 U.S. Service Pack 2. For more information, contact your primary support provider.

Additional query words: sysconstraints sysreferences

Keywords: kbbug KB153224

-

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

© Microsoft Corporation. All rights reserved.