imanshu15 imanshu15 - 5 months ago 95
SQL Question

SQL Server create table error - may cause cycles or multiple cascade paths

I am trying to create this activity table:

CREATE TABLE activity
(
activity_id int,
description varchar(300),
start_date date,
due_date date,
plannes_price float,
hours_worked int,
billing_rate float,
actual_price float,
status varchar(50),
employee_id int,
date_completed date,
service_id int,

CONSTRAINT pk_activityId PRIMARY KEY (activity_id),
CONSTRAINT fk_act_emp_id FOREIGN KEY (employee_id)
REFERENCES employee(employee_id)
ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT fk_act_ser_id FOREIGN KEY (service_id)
REFERENCES service(service_id)
ON UPDATE CASCADE ON DELETE NO ACTION
)


but I get this error:


Msg 1785, Level 16, State 0, Line 114

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

Msg 1750, Level 16, State 0, Line 114

Could not create constraint or index. See previous errors.


If I set
fk_act_ser_id
constraint
UPDATE NO ACTION
then the code will run with no errors. But I want to set it is UPDATE CASCADE.

Does anyone know how to fix this?

Answer

It's all about the tables that aren't shown here. There are multiple ways to delete an activity - and one of those ways has two paths to activity. For example if a service can result in the deletion of an employee (or an employee deletion can delete a service)...or some other delete path. You can only have one delete/update path to any table from a given starting table.

Comments