Write a check constraint on columns from two tables

Asked Dec 18 '15 at 22:52

Active Dec 16 '19 at 01:24

Viewed 49 times

I have this simple database I'm working on as assignment in university: I don't need full solution, I just need the way it work.

1: UsageCount : how many times they used the question in past exams.

2: CorrectAnswerCount : how many student Answered the question right.

3: StudentCount : The number of student took the exam.

My question is:

How do I make a check constraint (between QuestionInExam and Exam) enforcing that CorrectAnswerCount <= StudentCount?

Also, how do I raise the UsageCount for a question every time it is used in an exam?

edited Dec 16 '19 at 01:24

Dale K

asked Dec 18 '15 at 22:52

Fares B Bader

1

Which DBMS are you using? – Dec 18 '15 at 22:55
Weird, your question doesn't really sound like a question to me. – sstan Dec 18 '15 at 22:55
I'm using SQL server 2008 ... the question is how to make a check constraint between to tables ... I tried to do it but it didn't work ... – Fares B Bader Dec 18 '15 at 22:57
I would remove the relationship between Question and Course. You have a loop there. You can easily relate Question to Course through the Exam table. – Nikita Silverstruk Dec 18 '15 at 22:57
they give us the diagram like this in university :\ – Fares B Bader Dec 18 '15 at 22:59
For the second part of your question, I would recommend using a AFTER INSERT trigger to add 1 to the UsageCount after a record is inserted to QuestionInExam https://msdn.microsoft.com/en-us/library/ms189799.aspx?f=255&MSPPError=-2147217396 – Nikita Silverstruk Dec 18 '15 at 23:05

0 Answers0