Microsoft KB Archive/256307

= 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

-

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

© Microsoft Corporation. All rights reserved.