30. Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
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 above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the
FOREIGN KEY.
Answer: A
答案解析：
sys@TESTDB> CREATE TABLE orders
2 (ord_no NUMBER(2) CONSTRAINT ord_pk1 PRIMARY KEY,
3 ord_date DATE,
4 cust_id NUMBER(4));
Table created.
sys@TESTDB> CREATE TABLE ord_items
2 (ord_no NUMBER(2),
3 item_no NUMBER(3),
4 qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
5 expiry_date date CHECK (expiry_date > SYSDATE),
6 CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
7 CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no))
8 ;
expiry_date date CHECK (expiry_date > SYSDATE),
*
ERROR at line 5:
ORA-02436: date or system variable wrongly specified in CHECK constraint
A答案：sysdate不能用在check约束中，A正确
sys@TESTDB> CREATE TABLE ord_items2
2 (expiry_date date CHECK (expiry_date > SYSDATE));
(expiry_date date CHECK (expiry_date > SYSDATE))
*
ERROR at line 2:
ORA-02436: date or system variable wrongly specified in CHECK constraint
B答案：BETWEEN 可以用在check约束中，B错误
sys@TESTDB> CREATE TABLE ord_items1
2 (qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200));
Table created.
C答案：check约束可以作用在data类型的列上，C错误。
sys@TESTDB> CREATE TABLE ord_items3
2 (expiry_date date CHECK (expiry_date >to_date('2013-09-03','yyyy-mm-dd')));
Table created.
D答案：外键约束和字段可以和本表组合成主键
sh@TESTDB> create table orders
2 (ord_no number primary key);
Table created.
sh@TESTDB> create table ord_items
2 (ord_no number not null,
3 item_no number,
4 constraint ord_fk foreign key(ord_no) references orders(ord_no),
5 constraint it_pk primary key(ord_no,item_no));
Table created.