Geek Answers Handbook

Check if row can be deleted

I want to check if a row can be deleted from a table in SQL Server 2008 due to a foreign key violation, without trying to delete it.

Basically, I don’t want to show the user the delete button if they cannot delete it, because the key is used elsewhere.

I need this in many places in the application, so I really don’t need to manually record checks to make sure that it is safe to delete the line. Any suggestions on the best way to achieve this?

I use an entity structure to access data.

+5
source share
7 answers

. , - information_schema, , , .

, .

- , . , . , , , . , . , .

+2

, LEFT JOIN . CanDelete, , . COUNT , .

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 = ...


SIGN(C.Existence) AS HasChildRows
+2

. TryDelete(). . FK, false. , true false, , .

+1

, .

, , Entity1, Entity2. , IsReferenced, :

  • Entity1 true, Entity1 Entity2
  • Entity2 return ture, Entity1

, , , , , .Load() IsReferenced . , , "" .

/ "" , , .

+1

, 2 . , OM, .

, , , ...

- sysobjects , . SQL, .

0

, , .

, , , , .

0

, ( ) - "" , , , , - else , .

, "", . , "", , ( ), .

It is impossible to avoid such races. I will just let people try deleting if they want, but be prepared to deal with errors due to foreign keys.

0
source

More articles:


All Articles