OCP-1Z0-051 补充题库 第17题 约束的注意事项

一、原题

Evaluate the following SQL statements:




The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type

答案: B

二、题目翻译

评估下面的SQL语句:
上面的命令会执行失败，原因是什么?
A.BETWEEN子句不能被用在CHECK约束中。
B.SYSDATE不能被用在CHECK约束中。
C.ORD_NO和ITEM_NO不能被用在组合主键中，因为ORD_NO也是一个外键。
D.CHECK约束不能用在DATE类型的列上。

三、题目解析

B选项正确，SYSDATE不能用在CHECK约束中，因为SYSDATE是一个随时会变化的值，也就是一个不确定的值。而CHECK约束，在插入数据的时候，是需要做检查的。和一个不确定的值比较，是没法比的。

详见11g联机文档：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52205

摘录如下:
Restrictions on Check Constraints Check constraints are subject to the following restrictions:
    1.You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
    2.The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
    3.Conditions of check constraints cannot contain the following constructs:
        1) Subqueries and scalar subquery expressions
        2) Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
        3) Calls to user-defined functions
        4) Dereferencing of REF columns (for example, using the DEREF function)
        5) Nested table columns or attributes
        6) The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
        7) Date constants that are not fully specified