0

I have designed an diagram in sql server 2008. as you can see in the photo:

enter image description here

Now in Value table I set the cascading delete ON for Feature and ProductDetail but I see the error below:

'Feature' table saved successfully 'ProductDetail' table saved successfully 'Value' table - Unable to create relationship 'ValueFeature'. Introducing FOREIGN KEY constraint 'ValueFeature' on table 'Value' 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.

What is it wrong with my design?

Hamid Reza
  • 2,913
  • 9
  • 49
  • 76
  • possible duplicate of [Foreign key constraint may cause cycles or multiple cascade paths?](http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) – Mikael Eriksson Mar 16 '14 at 11:24

1 Answers1

2

I think I have faced the same problem some years ago, in SQL server 2005. I suppose that nothing is wrong with your design. If you delete a Feature you want all the Value records to be deleted. Nothing wrong with that. If you don't have other cascades, the automatic delete will not propagate further. However, it seems that SQL Server is not clever enough to understand that, and does not allow you to have such a relationship, just because you have foreign keys that form a circle. I think that if you remove a foreign key (just for testing) and break the circle (for example delete the FK between the ProductDetail and Product) there would be no error.

Check this stackoverflow question too..

Hope I helped!

Community
  • 1
  • 1
Pantelis Natsiavas
  • 5,293
  • 5
  • 21
  • 36