SQL Server：如何知道是否有任何行正在引用要删除的行 [英] SQL Server: how to know if any row is referencing the row to delete

问题描述

是否有可能知道是否有行正在引用要删除的行在执行一个DELETE语句前？

解决方案

这个脚本将显示所有引用您要删除的行的表：
$ b $ pre $ declare @RowId int = 1
declare @TableName sysname ='ParentTable'

声明@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
加入
上的sys.columns col fkc.parent_object_id = col.object_id和fkc.parent_column_id = col.column_id
where object_name（referenced_object_id）= @Ta bleName

执行（@Command）


假设外键不是合成的。

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?

解决方案

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.

这篇关于SQL Server：如何知道是否有任何行正在引用要删除的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！