PRB: Constraint Violation Is Not Detected If Update with the Same Data (238146)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

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

MORE INFORMATION

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.

Modification Type:	Major	Last Reviewed:	10/28/2003
Keywords:	kbBug kbpending kbprb KB238146