【OCP】Oracle 11g OCP 1Z0-051 001

1.View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables.

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.

Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)

AS

SELECT prod_id, cust_id, time_id

FROM sales;

Which statement is true regarding the above command?

A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

答案：B NEW_SALES表可以建立，並且所有指定列上的已定義的非空約束會傳遞給新表。
考點：使用子查詢建立表（create table as subquery）時，顯示建立的非空約束（explicitly not null）可以傳遞到新表，其他約束不會傳遞。
關鍵字：NOT NULL

【解析實驗】：
解鎖sh使用者
SYS@PROD>alter user sh identified by sh account unlock;
SYS@PROD>conn sh/sh


SH@PROD>create table new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
  2  as select prod_id, cust_id, time_id from sales;

表可以被建立，AC選項錯

檢視新表上約束
SH@PROD>desc new_sales;



SH@PROD>set linesize 100
SH@PROD>col table_name for a10
SH@PROD>col column_name for a15
SH@PROD>col constraint_name for a25
SH@PROD>col search_condition for a30

SH@PROD>select a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION
    from user_constraints a,user_cons_columns b
    where a.constraint_name=b.constraint_name and a.table_name='NEW_SALES';


檢視源表約束
SH@PROD>select a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION
  2  from user_constraints a,user_cons_columns b
  3  where a.constraint_name=b.constraint_name and a.table_name='SALES';

新表可以建立，原表上對應列上的not null約束傳遞到了新表，但其他約束（PROD_ID列上外來鍵約束FOREIGN KEY）沒有傳遞到新表。
D選項錯，B選項正確。

實驗結束，清理測試表
SH@PROD>drop table new_sales purge;


【官方文件】
【SQL Language Reference  -  create table  -  as subquery】
Oracle Database automatically defines on columns in the new table any NOT NULL constraints that have a state of NOT DEFERRABLE and VALIDATE, and were explicitly created on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. If any rows violate the constraint, then the database does not create the table and returns an error.

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values are not carried over to the new table.




【練習實驗】
建立帶有不同約束的測試表t1，c1,c2,c3上分別帶有主鍵、非空、唯一三種約束

SH@PROD>create table t1 (
  2  c1 number constraint t1_c1_pk primary key,
  3  c2 number constraint t1_c2_nn not null,
  4  c3 number constraint t1_c3_uk unique);

Table created.

檢視原始表t1上約束
SH@PROD>select a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION
  2  from user_constraints a,user_cons_columns b
  3   where a.constraint_name=b.constraint_name and a.table_name='T1';

TABLE_NAME COLUMN_NAME     CONSTRAINT_NAME           C SEARCH_CONDITION
---------- --------------- ------------------------- - ------------------------------
T1         C2              T1_C2_NN                  C "C2" IS NOT NULL
T1         C1              T1_C1_PK                  P
T1         C3              T1_C3_UK                  U



使用子查詢建立建立測試表t2
SH@PROD>create table t2 as select * from t1;

Table created.

檢視新表上約束
SH@PROD>select a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION
  2  from user_constraints a,user_cons_columns b
  3  where a.constraint_name=b.constraint_name and a.table_name='T2';


TABLE_NAME COLUMN_NAME     CONSTRAINT_NAME           C SEARCH_CONDITION
---------- --------------- ------------------------- - ------------------------------
T2         C2              SYS_C0011275              C "C2" IS NOT NULL


SH@PROD>desc t2;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 C1                                                             NUMBER
 C2                                                    NOT NULL NUMBER
 C3                                                             NUMBER