Knowledge Base

FIX: Error Message 547: "Update statement conflicted with TABLE.."

Article ID: 256307

Article Last Modified on 3/14/2006

APPLIES TO

Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q256307

BUG #: 53833 (SQLBUG_70)

SYMPTOMS

When you update a table that has a composite foreign key, and one of the columns in the foreign key constraint is NULL, the update fails with this error:

Server: Msg 547, Level 16, State 1, Line 1
Update statement conflicted with TABLE FOREIGN KEY constraint 'FK1'. The conflict occurred in database 'pubs', table 'Pk'.
The statement has been terminated.

As stated in SQL Server Books Online, you should skip the verification of the FOREIGN KEY constraint.

WORKAROUND

Here are two ways to work around this:

Define all the columns used in a foreign key constraint as NOT NULL.

-or-

Update all the columns used in a foreign key constraint in the same update statement.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Here is a script that reproduces the error:

CREATE TABLE Pk(
   Col1   VARCHAR ( 25 ) NOT NULL,
   Col2   VARCHAR ( 25 ) NOT NULL,
   CONSTRAINT Pk1
       PRIMARY KEY (Col1, Col2)
 )
 GO
 
 CREATE TABLE Fk (
   Pk1    VARCHAR ( 25 ) NOT NULL,
   Col1   VARCHAR ( 25 ) NOT NULL,
   Col2   VARCHAR ( 25 )     NULL,
   Col3   VARCHAR ( 25 )     NULL,
   CONSTRAINT FkPk1
       PRIMARY KEY (Pk1)
 )
 GO
 
 ALTER TABLE Fk
 ADD CONSTRAINT Fk1
 FOREIGN KEY (Col1, Col2 )
    REFERENCES Pk(Col1, Col2 )
 GO
 
 INSERT INTO Pk(Col1, Col2) VALUES ('ABC', 'Test' )
 GO
 
 INSERT INTO Fk (Pk1, Col1, Col2, Col3) VALUES ('ONE', 'ABC', NULL, NULL)
 GO
 
 UPDATE Fk
 SET Col1 = 'ABC'
 WHERE Pk1 = 'ONE'
 GO

The preceding update does not fail if the second column is also updated to NULL or a valid value; nor does it fail when the second field is not NULL (just update col1 when col2 is not null). However, this code works:

UPDATE LeeMacFk
    SET Col1 = 'ABC', col2=NULL
  WHERE Pk1 = 'ONE'
 go
--> works fine
 UPDATE LeeMacFk
    SET Col2 = 'Test'
  WHERE Pk1 = 'ONE'
 go
--> works fine
 UPDATE LeeMacFk
 SET Col1 = 'ABC'
 WHERE Pk1 = 'ONE'
 go
--> works fine..

The update fails only when one of the composite foreign key columns is NULL.

Additional query words: update foreign key

Keywords: kbbug kbfix KB256307

Microsoft KB Archive/256307

Contents