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.
答案:A
可以作为主键,因为他们是联合主键,如果这里只是将ord_no作为主键的话,就会有问题,
因为他上面已经存在了外键了
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.
答案:A
A:正确
SQL> create table test( a date ,constraints ck_a check(a<sysdate));
create table test( a date ,constraints ck_a check(a<sysdate))
ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误
B:错误,between可以用于check约束
SQL> create table test(a number,constraints ck_a check(a between 1 and 100));
Table created
C:错误,date类型的列可以使用check约束
SQL> create table test(a date,constraints ck_a check(a<to_date('2015-12-12','yyyy-mm-dd')));
Table created
SQL>
D:错误
可以作为主键,因为他们是联合主键,如果这里只是将ord_no作为主键的话,就会有问题,
因为他上面已经存在了外键了
--判断foreign 和primary
SQL> create table test1(a date,constraints pk_a primary key(a),
2 constraints fk_a foreign key(a) references test(a));
create table test1(a date,constraints pk_a primary key(a),
constraints fk_a foreign key(a) references test(a))
ORA-02270: 此列列表的唯一关键字或主键不匹配
--判断check 和primary
SQL> create table test1(a date,constraints pk_a primary key(a),
2 constraints ck_test1_a check(a<to_date('2015-12-12','yyyy-mm-dd')));
Table created
--判断unique和primary
SQL> create table test1(a date,constraints pk_a primary key(a),
2 constraints un_test1_a unique(a));
create table test1(a date,constraints pk_a primary key(a),
constraints un_test1_a unique(a))
ORA-02261: 表中已存在这样的唯一关键字或主键
--判断null和prmay
create table test1 ( a date primary key constraints nu_a null);
这里尽然可以??但是查看user_constraints却没有这个的信息
SQL> select constraint_name,constraint_type from user_constraints where table_name ='TEST1';
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
SYS_C0030903 P
--没有null约束的信息
SQL> select nullable from user_tab_columns where table_name='TEST1';
NULLABLE
--------
N
--不能为null
虽然成功了,但是找不到任何null约束的信息,也不起作用