OCP-1Z0-051 第1题 CTAS语句建表注意事项

一、 原题

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

二、题目翻译:

看下图中SALES, CUSTOMERS, PRODUCTS, and TIMES这几张表的结构，SALES表有一个外键列PROD_ID,关联到PRODUCTS表。类似地，CUST_ID and TIME_ID 列也是SALES表的外键列，分别关联到表CUSTOMERS和表TIMES,
下面的这条CREATE TABLE建表命令：
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
关于这个命令，下面哪一种说法是正确的：
A.NEW_SALES这张表不能创建成功，因为列定义时，不能指定默认值。
B.NEW_SALES这张表能创建成功，并且所有 指定列上的已定义的非空约束会带到新表。
C.NEW_SALES表不能创建成功，因为CREATE TABLE命令中的列名与SELECT子句中的列名不匹配。
D.NEW_SALES表可以创建成功，并且所有指定列上的已定义的外键约束会带新表。

三、题目解析:

        使用CREATE TABLE..AS..语句创建表时，原表列上如果有显式非空约束会传递给新表，其它约束都不会带到新表。

        11.2联机文档中的关于CREATE TABLE 的 AS subquery 里的具体描述：
         http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54626

摘录如下：

      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约束自动会带到新表。

      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.
       隐式的NOT NULL约束不会带到新表，如主键。

    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.
       另外，主键，唯一，外键，check约束，分区，索引以及列的默认值不会带到新表。

       If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way, partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired partitioning as part of the CREATE TABLE statement before the AS subquery clause.
     在新表上可以选择是否像旧表那样分区，或者不同的分区形式，或者不分区的表，分区不会被带到新表中, 在AS subquery句之前指定。