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

ID: Q153224

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

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 '<db name>', database '<table>', that violates FOREIGN KEY constraint '<constraint name>' being added ALTER command has been aborted

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:

The foreign key constraint was created using CREATE TABLE.

The foreign key column was not part of a primary key.

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:

Create the foreign key constraint using CREATE TABLE.

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.

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          : kbbug6.50 kbbug6.00 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 31, 1999