Oracle检查完整性约束

问题描述：

我想在Oracle中创建一个受几个完整性约束控制的表。该表由一个假设的学生数据库组成。数据库的规则之一是，对于被分类为初级学生的学生，他们必须完成55和84学分（含）之间。

I am trying to make a table in Oracle which is governed by several integrity constraints. The table consists of a hypothetical student database. One of the rules of the database is that for a student to be classified as a junior, they must have completed between 55 and 84 credit hours (inclusive).

我需要创建一个完整性约束来强制执行此规则，但不完全确定如何去做。我有一个感觉，CHECK约束将是有用的在这种情况下。

I need to create an integrity constraint to enforce this rule but am not entirely sure how to go about doing it. I have a feeling that a CHECK constraint would be useful in this situation.

到目前为止，我有...

So far I have...

CONSTRAINT IC4 CHECK (hours >=55 AND hours <= 84),

此代码有效，不确定学生记录是否为初级学生。

This code is valid, however it does not determine if the student record is a junior.

我的表的设置是...

The set up of my table is...

    CREATE TABLE  Students ( id                INTEGER, 
                             name              CHAR(10)    NOT NULL,  
                             classification    CHAR(10)    NOT NULL, 
                             hours             INTEGER, 
                             gpa               NUMBER(3,2) NOT NULL, 
                             mentor            INTEGER);

因此，如果我们尝试插入...

So if we try to insert...

INSERT INTO Students VALUES (50, 'Kim', 'junior', 34, 3.5, 40);

...将违反完整性约束，因为记录试图存储为但学生只完成了34小时。

...the integrity constraint would be violated because the record is trying to be stored as a 'junior' but the student has only completed 34 hours.

我如何编写一个强制执行这些规则的约束？

How would I go about writing a constraint which would enforce these rules?

答

您需要再次使用魔术词和 p>

You need to use the magic word and again:

CREATE TABLE students (
....
 , CONSTRAINT IC4 CHECK ( classification = 'junior' AND hours >=55 AND hours <= 84 )

我怀疑你会想要其他分类（和使用BETWEEN来定义范围的清晰度）....

I suspect you'll want to have other classifications too, and validate their ranges. Use parentheses and OR to do this. (And use BETWEEN to define the ranges for clarity)....

, CONSTRAINT IC4 CHECK ( ( classification = 'junior' AND hours BETWEEN 55 AND 84 ) 
                       OR ( classification = 'sophomore' AND hours BETWEEN 85 AND 124 )  
                       OR ( classification = 'senior' AND hours > 124 )  
                       OR ( classification = 'fresher' )
                       )

请确保您有一套完整的范围。

Make sure you have a complete set of ranges.

相关推荐