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

• Microsoft SQL Server, versions 6.0 and 6.5

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

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.