sql - 存储过程。检查约束是否在另一个表中使用

Question

我收到此错误消息：

DELETE 语句与 REFERENCE 约束“FK_FieldMapper_Field”冲突。冲突发生在数据库“SCAM”、表“dbo.FieldMapper”、列“FieldID”中。

我有一些具有给定数量的字段的访问助手。这些字段可以被多个访问助手使用。

当我删除一个accesshelper时，我需要检查fields给定accesshelper的是否被其他使用accesshelpers。如果是，我将删除 accesshelper，但不删除字段，因为这会破坏其他 accesshelper。我怎么做？

到目前为止，这是我自己想出的。

USE [SCAM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[deleteAccessHelperById]

@Id int
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @projectIds TABLE (id int);
    INSERT INTO @projectIds ([id]) SELECT dbo.AccessHelperMapper.ProjectID FROM dbo.AccessHelperMapper WHERE dbo.AccessHelperMapper.AccessHelperID = @Id;

    DELETE dbo.AccessHelperMapper  WHERE dbo.AccessHelperMapper.AccessHelperID = @Id;

    DECLARE @fieldIds TABLE (id int);

    INSERT INTO @fieldIds ([id]) SELECT dbo.FieldMapper.FieldID FROM dbo.FieldMapper WHERE dbo.FieldMapper.AccessHelperID = @Id;


    DECLARE @AHfields TABLE (id int)
    Insert into @AHfields 
    Select fids.id from dbo.FieldMapper, @fieldIds as fids
    where dbo.FieldMapper.AccessHelperID != @Id
    and fids.id != dbo.FieldMapper.FieldID;

    delete dbo.FieldMapper where dbo.FieldMapper.AccessHelperID = @Id and dbo.FieldMapper.FieldID IN (SELECT d.id FROM @AHfields as d);
    delete dbo.Field where dbo.Field.ID in (SELECT g.id FROM  @AHfields as g);
    delete dbo.AccessHelper where dbo.AccessHelper.ID = @Id;


END

标签： sqlsql-servertsqlstored-procedures

Answer 1

试试下面的代码。希望这是您正在寻找的：

USE [SCAM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[deleteAccessHelperById]

@Id int
AS
BEGIN

SET NOCOUNT ON;

DELETE dbo.AccessHelperMapper  WHERE dbo.AccessHelperMapper.AccessHelperID = @Id;

DECLARE @fieldIds TABLE (id int);

INSERT INTO @fieldIds ([id]) SELECT dbo.FieldMapper.FieldID FROM dbo.FieldMapper WHERE dbo.FieldMapper.AccessHelperID = @Id
AND dbo.FieldMapper.FieldID NOT IN (SELECT DISTINCT dbo.FieldMapper.FieldID FROM dbo.FieldMapper WHERE dbo.FieldMapper.AccessHelperID != @Id);

delete dbo.FieldMapper where dbo.FieldMapper.AccessHelperID = @Id --and dbo.FieldMapper.FieldID IN (SELECT d.id FROM @fieldIds as d);
delete dbo.Field where dbo.Field.ID in (SELECT g.id FROM  @fieldIds as g);
delete dbo.AccessHelper where dbo.AccessHelper.ID = @Id;


END