SQL Server: How to Know If Any Row Is Referencing the Row to Delete

SQL Server: how to know if any row is referencing the row to delete

This script will show all the tables that have rows that reference the row you are trying to delete:

declare @RowId int = 1
declare @TableName sysname = 'ParentTable'

declare @Command varchar(max) 

select @Command = isnull(@Command + ' union all ', '') + 'select ''' + object_name(parent_object_id) + 
    ''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')' 
from sys.foreign_key_columns fkc
    join sys.columns col on
        fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName

execute (@Command)

Assumption that foreign key is not composite.

How to know if a row in a table is being referenced in other tables before deletion?

DECLARE @TableName sysname = 'YourTable'

DECLARE @Command VARCHAR(MAX) 

SELECT @Command = isnull(@Command + ' UNION ALL ', '') + 
       'Select count(*) ,''' + SCHEMA_NAME(obj.schema_id)+'.'+OBJECT_NAME(fkc.parent_object_id) + ''' '+
       'From ' + SCHEMA_NAME(obj.schema_id)+'.'+OBJECT_NAME(fkc.parent_object_id) + ' '+
       'Where ' + col.name+ ' IS NOT NULL' 
from sys.foreign_key_columns fkc
INNER JOIN sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
INNER JOIN sys.objects obj ON obj.object_id = col.object_id
where object_name(referenced_object_id) = @TableName

execute (@Command)

How to programmatically check if row is deletable?

You can try to delete the row and roll back the effects. You wouldn't want to do that in a trigger function because any exception cancels all persisted changes to the database. The manual:

When an error is caught by an EXCEPTION clause, the local variables of
the PL/pgSQL function remain as they were when the error occurred, but
all changes to persistent database state within the block are rolled back.

Bold emphasis mine.

But you can wrap this into a separate block or a separate plpgsql function and catch the exception there to prevent the effect on the main (trigger) function.

CREATE OR REPLACE FUNCTION f_can_del(_id int)
  RETURNS boolean AS 
$func$
BEGIN
   DELETE FROM master WHERE master_id = _id; -- DELETE is always rolled back

   IF NOT FOUND THEN
      RETURN NULL;                        -- ID not found, return NULL
   END IF;

   RAISE SQLSTATE 'MYERR';                -- If DELETE, raise custom exception

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

This returns TRUE / FALSE / NULL indicating that the row can be deleted / not be deleted / does not exist.

db<>fiddle here

_{Old sqlfiddle}

One could easily make this function dynamic to test any table / column / value.

Since PostgreSQL 9.2 you can also report back which table was blocking.

PostgreSQL 9.3 or later offer more detailed information, yet.

Generic function for arbitrary table, column and type

Why did the attempt on a dynamic function that you posted in the comments fail? This quote from the manual should give a clue:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

It works with GET DIAGNOSTICS:

CREATE OR REPLACE FUNCTION f_can_del(_tbl regclass, _col text, _id int)
  RETURNS boolean AS 
$func$
DECLARE
   _ct int;                              -- to receive count of deleted rows
BEGIN
   EXECUTE format('DELETE FROM %s WHERE %I = $1', _tbl, _col)
      USING _id;                         -- exception if other rows depend

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN
      RAISE SQLSTATE 'MYERR';            -- If DELETE, raise custom exception
   ELSE
      RETURN NULL;                       -- ID not found, return NULL
   END IF;

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

db<>fiddle here

_{Old sqlfiddle}

While being at it, I made it completely dynamic, including the data type of the column (it has to match the given column, of course). I am using the polymorphic type anyelement for that purpose. See:

• How to write a function that returns text or integer values?

I also use format() and a parameter of type regclass to safeguard against SQLi. See:

• SQL injection in Postgres functions vs prepared queries

How to get all references which hinders delete of a row I'm trying to delete

basically I look at this as part of database/application architecture and you need to know as the developer or DBA how you tables are connected.

You need to use the customer ID and query against any table that uses customerID as a foreign key and that would give you a result.same with the contracts. You can do this with some ERD tools, but I tend to make my own classes to do this for my databases, this way I have total control of what I want to do. For instance in the delete method for a customer, a company may just want to disable or set active to false for the customer. or truly delete everything.

There is a way to cascade deletion in the database, but I am not sure you want to do that, since you are asking for a result to be returned.

TSQL, delete row and all foreign key related rows

Best way would be enabling CASCADE DELETE.
Read more here: How do I use cascade delete with SQL Server?

Elegant way to delete rows which are not referenced by other table

There's one notorious gotcha for not in. Basically, id not in (1,2,3) is shorthand for:

id <> 1 and id <> 2 and id <> 3

Now if your TimeEntries table contains any row with a TaskID of null, the not in translates to:

ID <> null and ID <> 1 and ID <> 2 AND ...

The result of a comparison with null is always unknown. Since unknown is not true in SQL, the where clause filters out all rows, and you end up deleting nothing.

An easy fix is an additional where clause in the subquery:

DELETE FROM Tasks 
WHERE  ID not IN 
       (
       SELECT  TaskID 
       FROM    TimeEntries 
       WHERE   TaskID is not null
       )

SQL Server - How to check a row can be deleteable before deleting

Assuming your requirement is you do not want to delete a row is it is referenced to from another table.

I would not leave this up to the c# code. What if another application is developed that does the delete or someone access the database directly.

First you should set up a delete rule on the relationship . Secondarily you could write a trigger to check before deleting and Handel it in there. Finally you could write you delete statement with a not exist for example

DELETE from TableA where ID in (select ID from tableA a left outer join tableB b on a.ID = b.ID where b.ID is NULL)

---

Related Topics