cycles or multiple cascade paths. SQL Error?

Question

I'm trying to create Employee database for practice. I'm getting this error:

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

What is causing this error?

create table Department(
    Dno int not null,
    Name_d varchar(30),
    primary key(Dno)
)

create table Employee(
    E_id int not null,
    F_name varchar(30),
    L_name varchar(30),
    B_date date,
    address_e varchar(30),
    salary int,
    Sex varchar(8),
    Cnic varchar(15),
    Email varchar(50),
    start_date_e date,
    primary key (E_id)
)

alter table Department add  Mgr_id int
alter table Department add constraint fk_mgr Foreign key(Mgr_id) references Employee(E_id) on update cascade on delete set null

alter table Employee add  Dno int
alter table Employee add constraint fk_dno Foreign key(Dno) references Department(Dno) on update cascade on delete set null

Possible duplicate of Foreign key constraint may cause cycles or multiple cascade paths? — Tab Alleman

Alfabravo Alfabravo · Accepted Answer · 2018-03-02T14:28:15

Having mutual foreign keys with ON UPDATE CASCADE creates a cycle because, if you modify a row from one, it searches for rows to delete / modify on the other table, which triggers modification in the other table (the one that originated the change) and so on.

Change the ON UPDATE and ON DELETE options for your FK. Evaluate if you need them mutually referencing fields (you might be designing with the wrong attributes)

cycles or multiple cascade paths. SQL Error?

1 Answers