0

I have the following tables. I have several dashboards. A dashboard can have several breakpoints. Each breakpoint has several widgets. If the dashboard is deleted, then all of the breakpoints for that dashboard also need to be deleted. When the breakpoint is deleted, the widgets for that breakpoint need to be deleted also.

CREATE TABLE dashboards (
    id INT IDENTITY(1,1) PRIMARY KEY,
    ...
);


CREATE TABLE breakpoints (
    id INT IDENTITY(1,1) PRIMARY KEY,
    dashboardId INT REFERENCES dashboards(id) ON DELETE CASCADE,
    ...
);


CREATE TABLE widgets (
    id INT IDENTITY(1,1) PRIMARY KEY,
    breakpointId INT REFERENCES breakpoints(id) ON DELETE CASCADE,
    ...
);

When I try to run the above code, I get an error:

Introducing FOREIGN KEY constraint 'FK__breakpoints__6225902D' on table 'widgets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

If I change it to NO ACTION then I don't get an error, but I need it to cascade.

How would I go about fixing this?

ToMakPo
  • 855
  • 7
  • 27
  • 1
    The above code does not result in multiple paths https://dbfiddle.uk/n2ESZLUh. Please show all other relevant tables and foreign keys. Probably it's not possible, but depends on your requirements – Charlieface Apr 29 '23 at 21:49
  • @Charlieface - Here is the full code. https://dbfiddle.uk/5VWGfm1B – ToMakPo Apr 29 '23 at 21:54
  • I checked your query and found two tables ( dashboardWidgetValues,breakpointWidgets ) That have two fields in both tables, whose relationships lead to one table, which wants to run delete mode from two paths, but it is not possible. You should either remove one or remove the condition or use an alternative way Table dashboardWidgetValues( 1.dashboardWidgetId =widgets(id) 2.parameterId=widgets(id)) Table breakpointWidgets( 1.dashboardBreakpointId=breakpoints_dashboards(id) 2.dashboardWidgetId=dashboardWidgets_dashboards) – abolfazl sadeghi Apr 29 '23 at 23:20

0 Answers0