Saif Ullah Saif Ullah - 5 months ago 109
SQL Question

visual studio SQL database, an error occurred while the batch was being executed

I have created the following table in service based database (Microsoft SQL) of my MS visual studio project:

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])
);


and after that when I try adding the following table I am getting the error:


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
);


There appears no warnings while creating update script and there is also no data already present in the database. I tried recreating everything from scratch but the error persists. What am I possibly doing wrong?

EDIT : The error disappears if I remove both "ON DELETE CASCADE" and "ON UPDATE CASCADE" from any one of the foreign key constraints, removing in any other combination doesn't helps. what's wrong with those constraints?

Answer

Given the documentation on MSDN about the error 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.

and their stated workaround

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

We could choose to avoid this problem creating a TRIGGER in this way

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

    DELETE FROM location WHERE Sender = (select number from deleted)
    DELETE FROM location WHERE Reciever = (select number from deleted)
END

And for the UPDATE another TRIGGER like this

ALTER TRIGGER [dbo].[CascadeUpdateNumber]
   ON  [dbo].[users]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    declare @old_num nvarchar(50)
    declare @new_num nvarchar(50)
    select @old_num = number from deleted
    select @new_num = number from inserted

    update location set sender = @new_num where sender = @old_num
    update location set reciever = @new_num where reciever = @old_num

END