Error from SQL Server

Question

Hello friends I am getting the following error while making the foreign key

'tbl_course' table saved successfully 'tbl_students' table - Unable to create relationship 'FK_tbl_students_tbl_course'.
Introducing FOREIGN KEY constraint 'FK_tbl_students_tbl_course' 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.

I have the following tables College table, Branch table, Course table and Student table

Course has a foreign key college_id, Branch has a foreign key course_id and I want to make college_id, course_id, branch_id as foreign keys to my Student table but while making course_id and branch_id as foreign key in Student table it generate the error mentioned above...please help me to resolve the above problem.....thanks

Answer 1

According to MS

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

http://support.microsoft.com/kb/321843

Can you live without the cascading deletes? Keep the referential ingegrity but not the referenctial actions (cascades). You could resort to a trigger as a work around if needed.

EDIT:

CREATE TRIGGER [dbo].[tr_College_Delete] ON [dbo].[College] 
FOR  DELETE 
AS 
BEGIN 
    DELETE  FROM student 
    where collegeid in (select collegeid from deleted) 

END 

not tested.

Answer 2

Why would you add anything but branch_id to the student? From there, you should be able to determine which course the branch belongs to and which college the course belongs to.

If you have

College
College1
College2
College3

Course
Course1, College1
Course2, College1
Course3, College2

Branch
Branch1, Course1
Branch2, Course1
Branch3, Course2

Student
Student1,College3,Course3,Branch1

This is valid from the database standpoint, but makes no sense since the student record should be attached to College1 based on the Branch it's attached to. You know logically that if the student is in a branch with ID Branch1 (or whatever primary key you use, but I use this for illustraion purposes) then they must be in Course1 and College1. By adding the additional information to the Student record, you not only create circular references in the code but put yourself in a position to create a "corrupt" record in the database. Of course you can code around this, but why go to the extra effort when you can simplify the student record to simply (Student1,Branch1) without losing any data?

This is known as database normalization, and it's something that's very important to at least consider when you're building a data model.