PostgreSQL -添加"constraint“到视图

Question

我曾尝试在can_graduate中添加一种方法，可以检查此人是否阅读了足够的sum_credits (180)来毕业，我想知道是否有人可以帮助我。在我检查Count(UnreadMandatory) =0的地方，我假设我想要添加像sum_credits >= 180这样的东西，但我无法让它工作。

这是我在这里的第一篇文章，所以请多多关照。

这就是我遇到麻烦的视图：https://pastebin.com/ezUtRMGB

    CREATE VIEW PathToGraduation AS
    SELECT student.national_ID,

    (SELECT sum(passedcourses.credit)
    FROM passedcourses, has_classification
    WHERE passedcourses.national_ID = student.national_ID AND has_classification.code = passedcourses.code) AS sum_credits,

    (SELECT count(UnreadMandatory)
    FROM UnreadMandatory
    WHERE UnreadMandatory.national_ID = student.national_ID) AS remaining_courses,

    (SELECT sum(passedcourses.credit)
    FROM passedcourses, has_classification
    WHERE passedcourses.national_ID = student.national_ID AND has_classification.code = passedcourses.code AND has_classification.type = 'Math_class') AS math_credits,

    (SELECT count(passedcourses.credit)
    FROM passedcourses, has_classification
    WHERE passedcourses.national_ID = student.national_ID AND has_classification.code = passedcourses.code AND has_classification.type = 'Seminar_class') AS seminar_courses_taken,

    (SELECT count(passedcourses.code)
    FROM passedcourses, has_classification
    WHERE passedcourses.national_ID = student.national_ID AND has_classification.code = passedcourses.code AND has_classification.type = 'Research_class') AS research_credits,

    (SELECT 'YES' AS can_graduate
    FROM UnreadMandatory
    WHERE student.national_ID = UnreadMandatory.national_ID
    HAVING COUNT(UnreadMandatory) = 0
    UNION
    (SELECT 'NO' AS can_graduate
    FROM UnreadMandatory
    WHERE student.national_ID = UnreadMandatory.national_ID
    HAVING COUNT(UnreadMandatory) != 0))

Answer 1

您可能正在寻找WITH CHECK OPTION。

根据文档。

此选项控制可自动更新的视图的行为。指定此选项时，将检查视图上的INSERT和UPDATE命令，以确保新行满足视图定义条件(即，检查新行以确保它们通过视图可见)。如果不是，更新将被拒绝。如果未指定CHECK选项，则允许视图上的INSERT和UPDATE命令创建在视图中不可见的行。