SQL Server:如何知道是否有任何行引用要删除的行

发布于 2024-11-14 19:42:37 字数 78 浏览 3 评论 0原文

如果任何行通过 FK 引用要删除的行,则无法删除该行。

在执行 DELETE 语句之前是否可以知道是否有行正在引用要删除的行?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

不语却知心 2024-11-21 19:42:37

此脚本将显示所有包含引用您要删除的行的行的表:

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)

假设外键不是复合键。

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.

念三年u 2024-11-21 19:42:37

选项 1(检测)

您执行 Select 语句 来确定是否有任何记录引用要删除的记录 - 如果您愿意, ,手动删除那些引用它的记录。这也可以使用触发器来完成,尽管我建议不要使用触发器,因为它们往往会给人们(和你自己)带来惊喜。

选项 2(自动化)

您可以查看 级联删除,如果配置正确,将导致引用要删除的记录的所有记录也被删除。

何时使用级联删除(摘自 Joel Coehoorn

  • 当关系的语义可能涉及“是”描述的一部分时,级联删除可能有意义。示例:Web 订单、Web 订单行项目
  • 如果要保留历史记录或使用仅设置已删除位列的软删除,则不应使用级联删除
  • 如果外键设置错误,级联可能会给您带来麻烦。
  • 在没有彻底理解之前使用级联是不明智的。然而,这是一个有用的功能,因此值得花时间去理解。

这里有关于 stackoverflow 上的级联删除的精彩讨论。

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.

埋葬我深情 2024-11-21 19:42:37

没有人提到它,但只是为了记录,我使用了很多过程

sp_helpconstraint 'dbo.mytable'

来查找与 dbo.mytable 相关的所有约束,以及哪些表引用 dbo.mytable。
我发现它非常有用且方便。

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.

非要怀念 2024-11-21 19:42:37

我改进了 Alex Aza 的解决方案。

我使用的是softdelete,所以有必要在条件“where”中添加一列“delete”。
我在 TSQL 中创建了一个函数,当表代表 NHibernate 中继承的对象时,它会发现该函数,并且 PK 是来自父表的 FK。

遵循:

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)

依赖函数:

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;

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;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文