3

Possible Duplicate:
Introducing FOREIGN KEY constraint 'c_name' on table 't_name' may cause cycles or multiple cascade paths

I have a person table

    person_id   name
    1           John
    2           Josh

I have a relationship table

   relationship_id  person_a  person_b
   1                1         2

I am creating a foreign key relationship between relationship.person_a -> person.person_id with ON UPDATE CASCADE. This works fine. Now I try to create a foreign key relationship between relationship.person_a -> person.person_id with UPDATE CASCADE, but I get an error from SQL Server as follows:

SQL ALTER TABLE [dbo].[relationship] ADD CONSTRAINT [person_b_is_person] FOREIGN KEY ([person_b]) REFERENCES [dbo].[person] ([person_id]) ON UPDATE CASCADE:

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

Why is this a problem?? Works on other databases like MySQL, Postgres. I want the ON UPDATE CASCADE so that if the person_id is changed, the relationships should be updated with the correct ids as well.

Community
  • 1
  • 1
sunbiz
  • 193
  • 2
  • 6
  • also answered [here](http://stackoverflow.com/questions/851625/foreignkey-constraint-may-cause-cycles-or-multiple-cascade-paths) – Thiago Dantas Sep 13 '11 at 14:19
  • It is also useful to note that such multiple cascade paths although allowed, is also not good for MySQL: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
    Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.
    – sunbiz Sep 14 '11 at 21:49

0 Answers0