在表“ t_name”上引入FOREIGN KEY约束“ c_name”可能会导致循环或多个级联路径 [英] Introducing FOREIGN KEY constraint 'c_name' on table 't_name' may cause cycles or multiple cascade paths
问题描述
我有一个名为 Lesson 的数据库表：
列： [LessonID，LessonNumber ，说明] ...以及其他一些列
I have a database table called Lesson:
columns: [LessonID, LessonNumber, Description] ...plus some other columns
我还有另一个名为 Lesson_ScoreBasedSelection ：
列： [LessonID，NextLessonID_1，NextLessonID_2，NextLessonID_3]
I have another table called Lesson_ScoreBasedSelection:
columns: [LessonID,NextLessonID_1,NextLessonID_2,NextLessonID_3]
课程完成后，在 Lesson_ScoreBasedSelection 表中查找其LessonID，以获取下三个可能的课程，每个课程均与特定范围相关联的分数。如果分数为0-33，则将使用存储在NextLessonID_1中的LessonID。如果分数是34-66，则将使用存储在NextLessonID_2中的LessonID，依此类推。
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.
我想限制 Lesson_ScoreBasedSelection 表中的每个值都必须在LessonID列中有一个条目课程表。我还希望打开级联更新，以便如果Lesson表中的LessonID发生更改，在 Lesson_ScoreBasedSelection 表中对其的所有引用都将得到更新。
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.
这个特定的级联更新似乎是一个非常简单的单向更新，但是当我尝试将外键约束应用于 Lesson_ScoreBasedSelection 引用了Lesson表中的LessonID字段的表，我得到了错误：
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:
在表'<$上引入FOREIGN KEY约束'c_name' c $ c> Lesson_ScoreBasedSelection '可能会导致循环或多个级联路径。
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?
推荐答案
鉴于此，SQL Server约束是，您为什么不通过创建一个具有SelectionID（PK），LessonID，Next_LessonID，QualifyingScore作为列的表来解决此问题。使用约束来确保LessonID和QualifyingScore是唯一的。
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.
在QualifyingScore列中，我将使用tinyint并将其设置为0、1或2。或您可以执行QualifyingMinScore和QualifyingMaxScore列，以便说
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
干杯，
Eric
Cheers,
Eric
这篇关于在表“ t_name”上引入FOREIGN KEY约束“ c_name”可能会导致循环或多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
