Foreign Key constraint issue, hard to find cause

Tweet

This hard to figure out issue has popped up from time to time

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Customer".
The conflict occurred in database "DATABASE", table "dbo.Order", column 'CustomerID'.

Not so much to do with the actual execution of your program, it works for most data but it seems that if a co-worker has manually edited data in the database with Sql Management Studio, there is a possibility that an extra line-break is introduced if the user confirms her edit with the return button.

To find the issue, copy the data and paste it to your favorite text-editor, in between two quotation marks.
Correct data:

'data'

Bad data:

'data
'

Remove the extra line break and you should be good to go.

Of course, good database design should prevent this from happening but sometimes you are stuck with legacy systems and it is good to know that this can happen, especially if you have people manually editing data in the database.

Hope this helps someone out there :)