OCP-1Z0-051 第31题 约束规则

一、原题

Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
                         INCREMENT BY 10
                         START WITH 120
                         MAXVALUE 9999
                         NOCYCLE;

SQL> CREATE TABLE ord_items
                 (ord_no NUMBER(4) DEFAULT ord_seq.NEXTVAL NOT NULL,
                 item_no NUMBER(3),
                         qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
           expiry_date date CHECK (expiry_date > SYSDATE),
           CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
           CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY

答案：AC

二、题目翻译

下面的SQL命令：
建表命令失败，标识命令失败的原因？（选择所有正确的选项）
A. SYSDATE不能用在CHECK约束中。
B. BETWEEN不能用在CHECK约束中。
C. 不能使用NEXTVAL序列值作为一个列的默认值。
D. 不能使用ORD_NO和ITEM_NO作为组合主键，因为ORD_NO是一个外键。

三、题目解析

B选项不正确，BETWEEN可以用于CHECK约束。
D选项不正确，没有这个限制，即使ORD_NO是一个外键，ORD_NO和ITEM_NO也可以组成组合主键。