patternsqlMinor
Should I drop a foreign key if I use a soft delete?
Viewed 0 times
deleteforeigndropshouldusekeysoft
Problem
"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
BTW: My application also validates existence of parent resource if child resource has a reference(foreign key) to it when creating child resource
Solution
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.
That's the general case; your specific business rules may be different.
Context
StackExchange Database Administrators Q#73340, answer score: 6
Revisions (0)
No revisions yet.