Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pshute
Starting Member
2 Posts |
 Posted - 2012-03-06 : 16:29:07
|
| I'm maintaining an existing application, and it recently started failing with a foreign key error on a certain operation.I can reproduce the error with a simple insert statement with fixed values. The key it's complaining about definitely exists in the other table, and I'm doing testing with a key that's been there for years.If I execute the same insert statement several times in a row, eventually it'll succeed. This to me proves that it's not about whether the foreign key exists or not, but there must be some other issue involved.If I then execute the statement again, same error, but if I keep trying, eventually it will work again. Sometimes it takes 5 or so tries, sometimes it takes 20 or 30.This is the test statement:INSERT INTO GrpIssue(IssueID, GrpID, TypeID, CreatorID, CreationDate)VALUES (1622, 6952, 65, 51092, GETDATE()) IssueID is the field that it's complaining about:Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GrpIssue_Issue". The conflict occurred in database "xyz", table "dbo.Issue", column 'IssueID'.Any clues about how to diagnose this problem? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
pshute
Starting Member
2 Posts |
Posted - 2012-03-06 : 17:26:39
|
quote:
Originally posted by tkizer
Are you saying that 1622 exists in the issue table when you get this error?
It certainly does exist, and in the meantime I've solved the problem.The foreign key constraint was wrong! Instead of checking if the issueID existed in the issueID column in table Issue, it was checking if GrpIssueID existed there, which is nonsensical. By coincidence, this could be guaranteed until recently, but not now.I've corrected the constraint, and now it's fine. This is not at all where I expected to find the problem. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Intermittent foreign key issue
Tara Kizer