0

I've been working on creating a table consisting of this...

  1. Module(ModuleID, ModuleTitle, ModuleLeader, Credits, CourseworkPercentage, ExamPercentage)

  2. ModuleID should be generated by a sequence

  3. ModuleTitle and ModuleLeader should have at most 30 characters and should be not null
  4. Credits should be not null and be 10, 20 or 40
  5. CourseworkPercentage and ExamPercentage should add up to 100

I have successfully done points 2,3,5 but I am having problems with 4. Not sure how I could write a check constraint to say it has to equal to either 10,20 or 40. Any help would be appreciated.

Improve this question

2 Answers 2

Reset to default
4

Try:

CHECK( Credits IS NOT NULL AND Credits IN ( 10,20,40))

NOT NULL condition is redundant - a column must be 10,20 or 40, all of them are not NULL, and the condition can be simplified into just:

CHECK( Credits IN ( 10,20,40) )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

It first looks like checking for NULL would be redundant, but I recently came across another post which asks to have re-look at the manual

[...]A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).[...]

So I suggest you better user 

CHECK( Credits IS NOT NULL AND Credits IN ( 10,20,40))
Improve this answer

Comments