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

最新推荐文章于 2022-10-11 14:21:54 发布

hello_holly

最新推荐文章于 2022-10-11 14:21:54 发布

阅读量3k

点赞数 1

分类专栏： OCP-1Z0-051 文章标签： OCP-1Z0-051 check constraint 约束组合主键

本文链接：https://blog.csdn.net/holly2008/article/details/32732079

版权

OCP-1Z0-051 专栏收录该内容

220 篇文章

订阅专栏

一、原题

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