The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.
obj = new_object()
obj.col1 = get_value_from_somewhere()
obj.user_id = get_logged_in_user_id()
obj.insert()
# Find correct ID.
obj.some_id = run_query("select some_id from tbl where x=?", param)
Here's the common scenario:
deals = sql("select * from current_deals ...");
// Show the user the deals.
// User takes his sweet time to ponder over the deals.
// Meanwhile, the deals are gone.
order = new_order()
order.deal_id = deals[5].id
order.insert()
// User calls in to complain never receiving the deal.
// Ok, need to verify the deal still exists.
sql("select deal from current_deals where id = ? for update", deals[5].id)
... insert ...
sql("commit")
We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows.
Certainly didn't help index scans...
Then there isn't really any opportunity for inconsistent data.