oracle ocp 051 14,【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 theNOT NULLconstraints 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.

答案：BNEW_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