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

ID: Q238146

The information in this article applies to:

Microsoft SQL Server version 7.0

SYMPTOMS

In SQL Server 7.0, 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 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.

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

Keywords : SSrvDRI kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbprb