> i am getting following error in sql
> server express while defining foreign
> key could any body resolve my problem
>
> > 'tbl_branch' table saved successfully
> > 'tbl_students' table
> > - Unable to create relationship 'FK_tbl_students_tbl_branch'.
> > Introducing FOREIGN KEY constraint
> > 'FK_tbl_students_tbl_branch' on table
> > 'tbl_students' 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.
> >
> > > Blockquote
views:
25answers:
1
+1
A:
I'm pretty sure this is warning that you have a "circular reference" with your foreign keys; i.e. tbl_branch has a foreign key that points to tbl_students and tbl_students has one that points to tbl_branch.
Specifically the problem is that you have cascading deletes or updates switched on. This means when you delete a row in tbl_branch, it'll try to delete the associated record in tbl_students, which will try to delete the associated record in tbl_branch, etc. In other words you may cause cylclical cascading deletions.
Either switch off cascading deletes/updates, or remove the "circular" foreign keys.
Rob Levine
2010-06-18 13:28:43
branch has a foreign key course_id ....can u tell me what is circular reference and how to resolve it...
2010-06-18 13:33:37
do these two tables both have a foreign key pointing to the other table?
Rob Levine
2010-06-18 13:44:39
A circular reference in its simplest form is when you have an FK from table A to table B and another from table B to table A. You could also have A->B->C->A, etc. It's a problem because if you have cascading updates for example, the server can get caught in an infinite loop.
Tom H.
2010-06-18 13:45:42
branch table has a foreign key course_id and i am trying to make branch table's branch_id as foreign key for student table .....basically i have structure like this ..i have tables college, course, branch, student.. i am making college_id a foreign key in course table and course_id a foreign key in branch table and in student table college_id and branch_id and course_id all three as foreign key ...but nun it works on except college_id when i define
2010-06-18 13:48:50
both course_id and branch_id as foreign key in student table it throws the same error mentioned in the above comment so if i remove on delete cascade and onupdate cascade then it works ..plesase suggest me what to do..
2010-06-18 13:50:06
Rob Levine..no i dont have any relation ship like A->B->A
2010-06-18 13:51:52
Then perhaps, indirectly, (as Tom H suggests) you have an A->B->C->A. That does seem to be what the database is warning you about.
Rob Levine
2010-06-18 14:02:32