DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 153224 - Last Review: November 1, 2013 - Revision: 4.0

This article was previously published under Q153224
This article has been archived. It is offered "as is" and will no longer be updated.
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

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.

Applies to
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug KB153224
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support