Check if it is safe to delete a row

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 withou开发者_运维知识库t 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.

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.

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

I've done this sort of thing in prior applications. I created a function named something like TryDelete(). Inside the method, I attempted to delete the desired row. If I got a FK exception, I caught it and returned false. In either case, true or false, I encapsulated the delete in a transaction and then rolled it back.

You could add in a partial class of your entity a method that would check if the referenced objects exist.

For example, lets say you have Entity1 which has collections of Entity2. Basically, in each of the entity partial classes you'd write a property IsReferenced that would:

• For Entity1 return true if Entity1 has any item in Entity2
• For Entity2 return ture if there's a reference to Entity1

As you're guessing, you'll need to make sure that you include referenced values always in your fetch, or, if you're working attached to context, you could use .Load() in IsReferenced to fetch entities before checking. It is an overhead, it just depends if you're willing to 'pay' for it.

Then, you can show/hide the 'delete' button based on that element property wherever needed thus avoiding having to repeat the checks each time.

I think you have 2 possible choices here.Since you cannot garantee that all relations will be mapped in your OM, you would have to check it on the database.

You can either try an actual delting inside a transaction that is rolled back afterwards, but this would also work if you have to contraint configured with cascading deletes...

Another way would be extracting all contraints from the sysobjects table, and verify that each table has no records. But that would require some dynamic SQL, which can also get quite messy.

if you're at the database level I would join all the tables where a conflict could exist.

any records that return can not be deleted which means the remaining set can be.

Assuming that the database is used by multiple users (which the vast majority are) - there's going to be a window of opportunity between "checking" the delete is possible, and the user possibly deciding to delete the row, during which someone else might perform some activity that negates the result of the test.

This means that you might display the Delete button, but by the time you attempt the delete, it's no longer possible. Also, you might not display a Delete button, but by the time the user has decided they want to delete the row (but can't find the button), they should be allowed to.

There's no way to avoid these kind of races. I'd just let people attempt the delete if they want to, but be prepared to deal with failures due to Foreign Keys.