Foreign Key Constraint Problem

[jfield817]

An SQL Server 2000 Foreign Key Constraint Anomaly is occuring.

The following tables exist in my database:

Person Table
Orders Table

Pers_id on Orders is a Foreign Key referencing Pers_id on Person Table

The following statements are executed in the following order:

Delete from Orders where Pers_id = 100
Delete from Person where Pers_id = 100

The SECOND statement produces a Foreign Key Constraint Violation

This should NOT occur since the Referencing record is deleted first........
This only happens on a few fluke records...
Clearly this SHOULD happen if an attempt was made to delete Person record first.

In order to workaround this problem I had to disable this Foreign Key Constraint. (which I don't want to do)

Very puzzling....... Any ideas ?

Thanks
John

 

[rasx]

It looks like you are rolling your own Cascading DELETEs. You should take it all the way and disable all enforcement of relationships.

In the SQL Server Enterprise Manager, select your database and find the Manage Relationships... button in Design Table or the Diagrams windows. Uncheck the box at Properties > Relationships > Enforce relationship for INSERTs and UPDATEs. This is a brute force way of solving the problem.


Bryan Wilhite
info@songhaysystem.com

http://www.songhaysystem.com

 

[jfield817]

Bryan,

Thanks for ur feedback ...
Im actually not trying to perform a cascading delete ..
since I am not deleting the Person first...

SQL 2000 does not appear to be behaving properly
here ...I know how to fix this anomaly by Nochecking
the constraint ...

But this behavior does not appear to be correct
and the app is crashing because of it

Thanks
John