>>I do want constraint but I want the message to the user to be simple and practical. So I guess what I am getting from this discussion is that I will need to "translate" the technical message from SQL Server into a more user-friendly one. I think I will use the name of the constraint as a key for "translation."
>>
>>>You cannot have it both ways. Either you have constrains or you don't.
>>>
>>>>My UI allows user to enter the data; so there is a potential for them to enter wrong value; nothing I can about it. If I give them only combo boxes for selecting from pull-down lists some won't be happy. Thank you for your input.
>>>>
>>>>>Make sure that your front end does not allow such situations. After that you can treat such errors as exemptions and log them as other errors.
>
>You could validate the FK from the UI before you commit the record(s). Since the FK will probably be part of an index on the parent a select to confirm the FK existence should be very quick, and easy to handle (if your qurey returns nothing, the value they entered is incorrect).

This is what I was doing in my VFP-back-end application. I guess I could call SQL Server primary table to see if it has the entry in the UNIQUE index. But I thought it would be redundant (slow things down) since the "invalid" entry does not happen every time. But it is something to consider. Thank you for your suggestion.