ocp-047-99 为啥DESC和VIEW显示不同。主键不能被带到新表，NOT NULL可以带到新表中。显示的可以，隐示不可以如主键。

99. View the Exhibit and examine the structure of the ORDERS table. 
The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following CREATE 
TABLE command: 
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cust_id) 
AS 
SELECT order_id,order_date,customer_id 
FROM orders; 
Which statement is true regarding the above command?

A. The NEW_ORDERS table would not get created because the DEFAULT value cannot be specified in 
the column definition. 
B. The NEW_ORDERS table would get created and only the NOT NULL constraint defined on the 
specified columns would be passed to the new table. 
C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE 
command and the SELECT clause do not match. 
D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns 
in the ORDERS table would be passed to the new table. 
Answer: B 

实验：

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cust_id) 
AS 
SELECT order_id,order_date,customer_id 
FROM orders  

既是：

create table NEW_ORDERS
(
  ORD_ID   NUMBER(12),
  ORD_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE default SYSDATE not null,
  CUST_ID  NUMBER(6) not null

)

注表orders

create table ORDERS
(
  ORDER_ID     NUMBER(12) not null,
  ORDER_DATE   TIMESTAMP(6) WITH LOCAL TIME ZONE,
  ORDER_MODE   VARCHAR2(8),
  CUSTOMER_ID  NUMBER(6),
  ORDER_STATUS NUMBER(2),
  ORDER_TOTAL  NUMBER(8,2),
  SALES_REP_ID NUMBER(6),
  PROMOTION_ID NUMBER(6)
)

alter table ORDERS
  add constraint ORDER_CUSTOMER_ID_NN
  check ("CUSTOMER_ID" IS NOT NULL);

SQL> desc new_orders;
Name     Type                              Nullable Default Comments 
-------- --------------------------------- -------- ------- -------- 
ORD_ID   NUMBER(12)                        Y                         
ORD_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE          SYSDATE          
CUST_ID  NUMBER(6)                                                   
 
SQL> desc orders;
Name         Type                              Nullable Default Comments                                                                                                                                                                                                                                                                                     
------------ --------------------------------- -------- ------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
ORDER_ID     NUMBER(12)                                         PRIMARY KEY column.                                                                                                                                                                                                                                                                          
ORDER_DATE   TIMESTAMP(6) WITH LOCAL TIME ZONE                  TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.                                                                                                                                                                                                                                  
ORDER_MODE   VARCHAR2(8)                       Y                CHECK constraint.                                                                                                                                                                                                                                                                            
CUSTOMER_ID  NUMBER(6)                                                                                                                                                                                                                                                                                                                                       
ORDER_STATUS NUMBER(2)                         Y                0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid 
ORDER_TOTAL  NUMBER(8,2)                       Y                CHECK constraint.                                                                                                                                                                                                                                                                            
SALES_REP_ID NUMBER(6)                         Y                References hr.employees.employee_id.                                                                                                                                                                                                                                                         
PROMOTION_ID NUMBER(6)                         Y                Sales promotion ID. Used in SH schema                                                                                                                                                                                                                                                        
 
SQL> insert into new_orders values (1,sysdate,null);
 
insert into new_orders values (1,sysdate,null)
 
ORA-01400: 无法将 NULL 插入 ("OE"."NEW_ORDERS"."CUST_ID")