c# - Visual Studio SQL 数据库，执行批处理时出错(消息 1785)

Question

我在我的 MS visual studio 项目的基于服务的数据库 (Microsoft SQL) 中创建了下表:

CREATE TABLE [dbo].[users] (
    [phone_number]   VARCHAR (50) NOT NULL,
    [name]     VARCHAR (50) NOT NULL,
    [picture]  VARCHAR (50) NOT NULL,
    [password] VARCHAR (50) NOT NULL, 
    CONSTRAINT [PK_users] PRIMARY KEY ([phone_number])
);

然后当我尝试添加下表时出现错误:

an error occurred while the batch was being executed

CREATE TABLE [dbo].[location] (
    [location_id] INT  NOT NULL PRIMARY KEY IDENTITY (1, 1),
    [sender]      VARCHAR (50) NOT NULL,
    [reciever]    VARCHAR (50) NOT NULL,
    [latitude]    FLOAT (53)   NOT NULL,
    [longitude]   FLOAT (53)   NOT NULL,
    CONSTRAINT [FK_1] FOREIGN KEY ([sender]) REFERENCES [dbo].[users] ([phone_number]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_2] FOREIGN KEY ([reciever]) REFERENCES [dbo].[users] ([phone_number]) ON DELETE CASCADE ON UPDATE CASCADE
);

创建更新脚本时没有出现警告，数据库中也没有数据。我尝试从头开始重新创建所有内容，但错误仍然存​​在。我可能做错了什么？

编辑: 如果我从任何一个外键约束中删除“ON DELETE CASCADE”和“ON UPDATE CASCADE”，错误就会消失，删除任何其他组合都没有帮助。这些限制有什么问题？

Answer 1

鉴于 MSDN 上关于 Msg 1785 的文档

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

及其规定的解决方法

You can enforce referential integrity in several ways. Declarative Referential Integrity (DRI) is the most basic way, but it is also the least flexible way. If you need more flexibility, but you still want a high degree of integrity, you can use triggers instead

我们可以选择以这种方式创建 TRIGGER 来避免这个问题

ALTER  TRIGGER [dbo].[CascadeDeleteNumber]
   ON  [dbo].[users]
   INSTEAD OF DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    DELETE FROM location WHERE Sender = (select phone_number from deleted)
    DELETE FROM location WHERE Reciever = (select phone_number from deleted)
    DELETE FROM users WHERE phone_number = (select phone_number from deleted)

END

DELETE 触发器是一个 INSTEAD OF，因为 AFTER 触发器永远不会被引用完整性检查阻止执行。 INSTEAD OF 允许我们先删除 location 表中的记录，然后删除 users 表中的记录，从而使参照完整性满意

对于 UPDATE，我们可以使用另一个 TRIGGER，但它更棘手。
我还没有找到一种在不破坏参照完整性的情况下正确更新 phone_number 的方法，因此我设法更改了 phone_number，暂时删除了参照完整性，更新了所需的记录，然后恢复了参照完整性。

ALTER TRIGGER [dbo].[CascadeUpdateNumber]
   ON  [dbo].[users]
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @old_num nvarchar(50)
    DECLARE @new_num nvarchar(50)
    DECLARE @new_name varchar(50)
    DECLARE @new_img varchar(50)
    DECLARE @new_pwd varchar(50)
    SELECT @old_num = phone_number FROM deleted
    SELECT @new_num = phone_number, @new_name = name, @new_img = picture,
           @new_pwd = password FROM inserted


ALTER TABLE [dbo].[location] DROP CONSTRAINT [FK_1]
ALTER TABLE [dbo].[location] DROP CONSTRAINT [FK_2]

    UPDATE location SET sender = @new_num WHERE sender = @old_num
    UPDATE location SET reciever = @new_num WHERE reciever = @old_num
    UPDATE users SET phone_number = @new_num, name = @new_name, 
                 picture=@new_img, password=@new_pwd 
                 WHERE phone_number = @old_num

ALTER TABLE [dbo].[location]  WITH CHECK 
       ADD  CONSTRAINT [FK_3] FOREIGN KEY([sender])
            REFERENCES [dbo].[users] ([phone_number])

ALTER TABLE [dbo].[location]  WITH CHECK 
       ADD  CONSTRAINT [FK_4] FOREIGN KEY([reciever])
            REFERENCES [dbo].[users] ([phone_number])

END

从表上并发更新的角度来看，我真的不知道这种方法是否安全。