Here is a simplified version of the database I am attempting to create:
create table Worker ( ID int, constraint PKW primary key (ID) ) create table Business ( ID int, W int, constraint PKB primary key (ID), constraint FKBW foreign key (W) references Worker (ID) on delete set null on update cascade ) create table Note ( ID int, B int, W int, constraint PKN primary key (ID), constraint FKNB foreign key (B) references Business (ID) on delete cascade on update cascade, constraint FKNW foreign key (W) references Worker (ID) on delete set null on update cascade )
Essentially each business has a worker assigned to it, and any worker has the ability to create a note regarding any business. I have 3 foreign key constraints to reflect these relationships, but this creates the error:
Introducing FOREIGN KEY constraint 'FKNW' on table 'Note' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
There are no relationship cycles in this setup. I can see how having FKBW
on delete cascade would create 2 delete paths to Note, but not with my current setup. Even if I remove
on update cascade from both FKBW and FBNW, the error persists. I don't see how
set null creates any problems.
Any clarity would be appreciated!