CODEWITHSUNDEEP
mysqlsqldatabase
Danijel
Danijel

Reputation: 8610

How to check if foreigh key violation will be triggered before executing SQL query?

Before I execute SQL query, I'd like to check if foreign key constraint will be violated. Is this possible?

Upvotes: 0

Views: 69

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

You can check for the foreign key's existence with a SELECT statement. To do that in a completely transparent way, you'd need to write code that examines either system tables or information_schema views, builds the right SELECT statement on the fly, then executes it and examines the result. You can hardcode most of that stuff, but only at the risk of executing code that's based on assumptions that no longer exist. (FKs might be dropped, tables renamed, etc.)

But there are some problems with both those approaches.

  • You have to put that SELECT statement (or statements) and the INSERT or UPDATE statement within a single transaction. Otherwise, you risk verifying the existence of a value that someone else deletes before your update. In other words, your SELECT succeeds, someone else's DELETE (or UPDATE) succeeds, your INSERT (or UPDATE) should succeed, but fails because of bad transaction handling.
  • The server has to process two queries for every INSERT or UPDATE statement. (And that doesn't count queries against the system tables.)
  • You still have to trap errors at the application level anyway. There are a lot of things that can prevent a sensible transaction from committing.

Since you have to trap errors anyway, it usually makes more sense to just issue the INSERT or UPDATE statement, and trap the error that says you violated a foreign key constraint.

Upvotes: 2

Related Questions