Foreign Key referencing multiple tables in a Question Bank scenario
Is QuestionNumber a unique identifier somehow for table Questions? Or is the QuestionBank designed to have, say, 5 different sets of questions with numbers 1 through 25?

If Questions.QuestionNumber is a mere non-unique ordering value for a given BankId, then there's no way to know, when saving a QuizQuestionStudent record, what QuestionBank a particular question belongs to without presuming that the answer is for a question in the proper bank. When QuestionNumber = 5, you'd have no idea how to check its BankId if multiple banks had a fifth question. You could presume it belongs to the same BankId as the Quiz, but then what is left for the database to check?

Now, if QuestionNumber is unique, you have a different problem. You want the Question's BankId to be required to be the same as the Quiz's BankId. Repeating the BankId in the QuizQuestionStudent table wouldn't help, because that value is coming from the app and it's what you're trying to check. You want to make sure data in two other tables conforms not just to QuizQuestionStudent values, but also to each other's values. You can't do that with a foreign key. (A significantly different design for all of the tables might eliminate the issue, but I wasn't able to come up with one.)

However, it can still be strongly enforced at the database layer. You can create an "indexed view" or "materialized view" and then constrain its values. The view can reference multiple tables. It would pull the BankId from two different paths and make sure they are the same.

CREATE VIEW QuizValidityCheck
WITH SCHEMABINDING
AS
(
SELECT
1 AS DupeCheckValue
FROM QuizQuestionStudent qqs
INNER JOIN Quizzes qz ON qqs.QuizId = qz.QuizId
INNER JOIN Questions qu ON qqs.QuestionNumber = qu.QuestionNumber
CROSS JOIN (VALUES (0),(1)) AS RowExploder(ExploderRow) -- this forces there to be 2 rows any time there's a BankId mismatch, quick causes the unique constraint below to fail
WHERE qu.BankId &lt;&gt; qz.BankId
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_CHECK_BankIdMismatch ON QuizValidityCheck(DupeCheckValue)
GO


You can also use a UDF to enforce a custom CHECK constraint on a column, which could go and check the other. But there are some scenarios where that falls apart.