32. Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Answer: D
答案解析
A，一个表只有一个主键。A错。
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) PRIMARY KEY,
3 item_no NUMBER(3) PRIMARY KEY,
4 ord_date DATE NOT NULL);
item_no NUMBER(3) PRIMARY KEY,
*
ERROR at line 3:
ORA-02260: table can have only one primary key
B答案：UNIQUE, NOT NULL之间不要逗号，列级约束不要逗号，B错
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) UNIQUE, NOT NULL,
3 item_no NUMBER(3),
4 ord_date DATE DEFAULT SYSDATE NOT NULL);
(ord_no NUMBER(2) UNIQUE, NOT NULL,
*
ERROR at line 2:
ORA-00904: : invalid identifier
逗号去掉可以创建，成功
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) UNIQUE NOT NULL,
3 item_no NUMBER(3),
4 ord_date DATE DEFAULT SYSDATE NOT NULL);
Table created.
C答案，两处错误，一个DEFAULT 后面没有指定默认值，一个是ord_no列的UNIQUE 和PRIMARY KEY
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) ,
3 item_no NUMBER(3),
4 ord_date DATE DEFAULT NOT NULL,
5 CONSTRAINT ord_uq UNIQUE (ord_no),
6 CONSTRAINT ord_pk PRIMARY KEY (ord_no));
ord_date DATE DEFAULT NOT NULL,
*
ERROR at line 4:
ORA-00936: missing expression
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) ,
3 tem_no NUMBER(3),
4 ord_date DATE DEFAULT sysdate NOT NULL,
5 CONSTRAINT ord_uq UNIQUE (ord_no),
6 CONSTRAINT ord_pk PRIMARY KEY (ord_no));
CONSTRAINT ord_uq UNIQUE (ord_no),
*
ERROR at line 5:
ORA-02261: such unique or primary key already exists in the table
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2) ,
3 tem_no NUMBER(3),
4 ord_date DATE DEFAULT sysdate NOT NULL,
5 CONSTRAINT ord_pk PRIMARY KEY (ord_no));
Table created.
D答案，正确
sys@TEST0910> CREATE TABLE ord_details
2 (ord_no NUMBER(2),
3 item_no NUMBER(3),
4 ord_date DATE DEFAULT SYSDATE NOT NULL,
5 CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Table created.