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

Question

I have a database table called Lesson:
columns: [LessonID, LessonNumber, Description] ...plus some other columns

I have another table called Lesson_ScoreBasedSelection:
columns: [LessonID,NextLessonID_1,NextLessonID_2,NextLessonID_3]

When a lesson is completed, its LessonID is looked up in the Lesson_ScoreBasedSelection table to get the three possible next lessons, each of which are associated with a particular range of scores. If the score was 0-33, the LessonID stored in NextLessonID_1 would be used. If the score was 34-66, the LessonID stored in NextLessonID_2 would be used, and so on.

I want to constrain all the columns in the Lesson_ScoreBasedSelection table with foreign keys referencing the LessonID column in the lesson table, since every value in the Lesson_ScoreBasedSelection table must have an entry in the LessonID column of the Lesson table. I also want cascade updates turned on, so that if a LessonID changes in the Lesson table, all references to it in the Lesson_ScoreBasedSelection table get updated.

This particular cascade update seems like a very straightforward, one-way update, but when I try to apply a foreign key constraint to each field in the Lesson_ScoreBasedSelection table referencing the LessonID field in the Lesson table, I get the error:

*Introducing FOREIGN KEY constraint 'c_name' on table 'Lesson_ScoreBasedSelection' may cause cycles or multiple cascade paths.*

Can anyone explain why I'm getting this error or how I can achieve the constraints and cascading updating I described?

Answer 1

You can't have more than one cascading RI link to a single table in any given linked table. Microsoft explains this:

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.

Answer 2

Given the SQL Server constraint on this, why don't you solve this problem by creating a table with SelectionID (PK), LessonID, Next_LessonID, QualifyingScore as the columns. Use a constraint to ensure LessonID and QualifyingScore are unique.

In the QualifyingScore column, I'd use a tinyint, and make it 0, 1, or 2. That, or you could do a QualifyingMinScore and QualifyingMaxScore column so you could say,

SELECT * FROM NextLesson 
WHERE LessonID = @MyLesson 
AND QualifyingMinScore <= @MyScore 
AND @MyScore <= QualifyingMaxScore

Cheers,
Eric