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

Question

You cannot delete a row if any row is referencing the row to delete via a FK.

Is it possible to know if any row is referencing the row to delete before executing a DELETE statement?

Orthoclase

possible duplicate of Help on SQL Server trigger – Copyist

My goal is not to cause an cascading delete, but to know beforehand if the record is deletable. If it is not deletable, the user will be notified that it's not deletable. – Orthoclase

I know you've already marked an answer, but if this is a multi-user system, the best answer may be to just attempt the delete and cope with an error occurring - otherwise there are all kinds of race conditions possible here, unless you wrap everything in a transaction with high isolation. – Jefferyjeffie

@Jefferyjeffie Thank you for your advice. I am adding the warning as an addition. I am also handling the error from SQL when the delete is attempted. – Orthoclase

Answer 1

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.

Colfin

Slight improvement for SQL Server would be to surround each object_name(...) and col.name with square brackets in the dynamic SQL to avoid reserved word problems. – Orphism

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From. – Cantwell

Answer 2

Option 1 (Detection):

You perform a Select Statement to determine if any records are referencing the Record-to-be-deleted -- and, if you wish, manually delete those records that do reference it. This can also be accomplished using a trigger, although I recommend against triggers, because they tend to surprise people (and yourself) down the road.

Option 2 (Automation):

You can look into Cascading Deletes which, if configured correctly, will cause all records referencing the Record-to-be-deleted to also be deleted.

When to use Cascading Deletes (Paraphrased from text written by Joel Coehoorn)

• Cascade Delete may make sense when the semantics of the relationship can involve an "is part of" description. Example: Web Order, Web Order Line Items
• You should not use Cascade Delete if you are preserving history or using a soft delete where you only set a deleted bit column
• Cascading can get you into trouble if you set up your foreign keys wrong.
• It's not wise to use cascading before you understand it thoroughly. However, it is a useful feature and therefore worth taking the time to understand.

Here's a great discussion on Cascading Deletes on stackoverflow.

Gilli

when you try to delete a record with a FK, SQL Server throws an exception. IS SQL Server internally performing a SELECT statement to know if there's any record referencing it? – Orthoclase

SQL keeps track of relationships and constraints in a variety of ways, some of those ways are similar to select statements. However, I think of it being more like garbage collection. "Nothing is referencing this chunk of memory any longer, therefore I can delete it" – Gilli

Answer 3

nobody has mentioned it, but just for the record I use a lot the procedure

sp_helpconstraint 'dbo.mytable'

in order to find all the constraints related to dbo.mytable, and which tables reference dbo.mytable. I find it very useful and handy.

Grainy

Answer 4

I improved the Alex Aza's solution.

I use softdelete, so It's necessary add a column "delete" in the condition "where" . And more I made a function in TSQL that it discovers when the table represents the object inherited in NHibernate, and the PK is the FK from parent table.

Follow:

declare @RowId int = 4
declare @TableName sysname = 'TABLE'

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) + 
    CASE
        WHEN EXISTS(select object_name(object_id) from sys.columns col where name = 'deleted' and object_id = parent_object_id) 
            THEN ' where ' + col.name+ ' = ' + cast(@RowId as varchar) +' and deleted = 0 '
        when dbo.ParentIdFromTable(object_name(parent_object_id)) <> ''
            then ' inner join ' + dbo.ParentIdFromTable(object_name(parent_object_id)) + ' on id = ' + dbo.PrimaryKey(object_name(parent_object_id))
                +' where ' + col.name+ ' = ' + cast(@RowId as varchar) +' and deleted = 0 '
        else 
            ' where ' + col.name+ ' = ' + cast(@RowId as varchar) 
      END
    + ')' 
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

PRINT @Command
execute (@Command)

Depedencies Functions:

CREATE FUNCTION dbo.ParentIdFromTable(@Table varchar(255))
RETURNS varchar(255) 
AS 
BEGIN
    declare @tableParent varchar(255) = ''

    if exists(select pk.TABLE_NAME, pk.COLUMN_NAME, col.name, object_name(referenced_object_id) Referenced, object_name(parent_object_id) as Parent
        from sys.columns col
            inner join sys.foreign_key_columns fkc on
                fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk on
                pk.TABLE_NAME = object_name(object_id) and pk.COLUMN_NAME = col.name
        WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
        AND table_name = @table)
    begin

        while exists(select *
            from sys.columns col
            inner join sys.foreign_key_columns fkc on
                fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk on
                pk.TABLE_NAME = object_name(object_id) and pk.COLUMN_NAME = col.name
            WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @table)
        begin
            -- Descobrir o parent, column
            select  @tableParent = object_name(referenced_object_id)
            from sys.columns col
            inner join sys.foreign_key_columns fkc on
                fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk on
                pk.TABLE_NAME = object_name(object_id) and pk.COLUMN_NAME = col.name
            WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
            AND table_name = @table

            --print @tableParent
            set @table = @tableParent
        end
    end

    return @tableParent;
END;
GO


CREATE FUNCTION dbo.PrimaryKey(@Table varchar(255))
RETURNS varchar(255) 
AS 
BEGIN
    declare @columnName varchar(255) = ''
    -- Descobrir o parent, column
    select @columnName = COLUMN_NAME
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
    AND table_name = @Table

    return @columnName
end;

Buzzard