SQL Server cascade on delete not working for two fk relations

Asked Jan 19 '15 at 04:17

Active Jan 19 '15 at 05:22

Viewed 91 times

I have a table messageTable with this structure:

id,
fromId,
toId,
text

and another table profileTable with these columns:

profileId,
name,
location

profileId is a foreign key referred to from the messageTable for the columns fromId and toId.

I want to add option for cascade rule on delete for both fromId and toId. But I get this error:

'Profile' table saved successfully
'Message' table
- Unable to create relationship 'FK_Message_Profile1'.
Introducing FOREIGN KEY constraint 'FK_Message_Profile1' on table 'Message' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

I have tried in mysql, it's working fine there. But it is not working in SQL Server 2008

edited Jan 19 '15 at 05:22

marc_s

732,580
175
1,330
1,459

asked Jan 19 '15 at 04:17

M14

1,780
2
14
31

1

Can you please provide the script that you execute to generate that error?. – Juan Ruiz de Castilla Jan 19 '15 at 04:24
ON DELETE CASCADE, SET DEFAULT, or SET NULL cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table that is being altered. please check it once – koushik veldanda Jan 19 '15 at 05:59
Seems this is a limitation of SQL Server (works on Postgres and Oracle as well) – Jan 19 '15 at 07:17

SQL Server cascade on delete not working for two fk relations

0 Answers0