在表 '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, Description] ...加上其他一些列

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 表中查找其课程 ID，以获取三个可能的下一课，每节课都与特定范围的分数相关联.如果分数是 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.

我想用引用课程表中的 LessonID 列的外键来约束 Lesson_ScoreBasedSelection 表中的所有列，因为 Lesson_ScoreBasedSelection 表中的每个值都必须有一个课程表的课程 ID 列中的条目.我还希望打开级联更新，这样如果 LessonID 表中的 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 表中引用课程表中的 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:

在表Lesson_ScoreBasedSelection"上引入 FOREIGN KEY 约束c_name"可能会导致循环或多个级联路径.

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

干杯，
埃里克

这篇关于在表 't_name' 上引入 FOREIGN KEY 约束 'c_name' 可能会导致循环或多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！