Microsoft KB Archive/238146

= PRB: Constraint Violation Is Not Detected If Update with the Same Data =

Article ID: 238146

Article Last Modified on 10/28/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q238146



BUG #: 56110 (SQLBUG_70)

BUG #: 212952(SHILOH)



SYMPTOMS
In SQL Server 7.0 or later, you do not receive an error message if you perform an update by setting a column to itself against a table that contains FOREIGN KEY constraint violation data. However, in SQL Server 6.5, you receive the following error:

547 16 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint '%.*s'.The conflict occurred in database '%.*s', table '%.*s'



CAUSE
This is by design. SQL Server 7.0 or later no longer updates a column with the same value because the "key" is not changing.



WORKAROUND
To work around this problem, explicitly re-enable constraints, as in the following example: ALTER TABLE titleauthor with check check constraint all go



Steps to Reproduce the Problem
To reproduce this problem, run the following script: USE pubs go

ALTER TABLE titleauthor nocheck constraint all go

UPDATE titleauthor SET title_id='invalid' WHERE title_id='BU1111' go

ALTER TABLE titleauthor check constraint all go

UPDATE titleauthor SET title_id = title_id go RESULTS: You do not get an error even if a table contains constraint violation data.

Additional query words: err msg violate const constr fkey fk valid invalid validate

Keywords: kbbug kbprb kbpending KB238146

-

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

© Microsoft Corporation. All rights reserved.