Check if it is safe to delete a row

Question

I want to be able to check if deleting a row from a table in SQL Server 2008 will fail because of a Foreign Key violation without attempting to delete it.

Basically, I don't want to show the user a delete button if they are not going to be able delete it because the key is used elsewhere.

I am needing this in many places in the application so don't really want to have to write the checks manually to see if it safe to delete the row. Any suggestions on the best way to achieve this?

I am using entity framework for access to the data.

Answer 1

There is no quick and easy way to check this. You could probably build something dynamic using information_schema, but it will undoubtedly be ugly and not very fast.

The absolute best choice is the few lines of custom code that it takes to verify per location.

Another option would be to start a transaction, try the delete. If it fails, then you know. If it succeeds, roll back the transaction, and you know the delete is possible. This is still ugly and is using transactions in a somewhat broken way, but it would work. Make sure cascade deletes aren't on for the table, though.

Answer 2

When you query, do a LEFT JOIN to the child table. Use CanDelete computed value to decide if the button should be shown. The COUNT here removed duplicates if you more than 1 child row per parent row.

SELECT
   Col1, Col2, Col3, ...,
   CASE C.Existence WHEN 0 THEN 1 ELSE 0 END AS CanDelete
FROM
   ParentTable P
   LEFT JOIN
   (
   SELECT COUNT(*) AS Existence, FKColumn
   FROM Childtable GROUP BY FKColumn
   ) C ON P.FKColumn = C.FKColumn
WHERE
   P.Col = ...

Another way might be

SIGN(C.Existence) AS HasChildRows