"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have suffered deadlock issue when application is under high concurrent workload.
BTW: My application also validates existence of parent resource if child resource has a reference(foreign key) to it when creating child resource
Keep it. A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table. Cascading deletes or updates are, generally, a distant second.
That's the general case; your specific business rules may be different.
I have an old post Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. In that post, I do not use a foreign key constraint in my example.
Soft deletes are way faster. You just have to integrate JOINs for tombstone tables or AND deleted=0 in WHERE clauses.
In light of these things, you probably could live without the foreign key reference if it does cause deadlock issues and you do the constraint check at the application level.
