OCP-1Z0-051-V9.02-34题

34. You created an ORDERS table with the following description:

name                Null            Type

ORD_ID              NOT NULL        NUMBER(2)

CUST_ID             NOT NULL        NUMBER(3)

ORD_DATE            NOT NULL        DATE

ORD_AMOUNT          NOT NULL        NUMBER (10,2)

You inserted some rows in the table. After some time, you want to alter the table by creating the

PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?

A. You cannot have two constraints on one column. 

B. You cannot add a  primary key constraint if data exists in the column.

C. The primary key constraint can be created only at the time of table creation .

D. You can add the  primary key constraint even if data exists,  provided that   there are  no duplicate

values. 

Answer: D

答案解析：

参考：http://blog.csdn.net/rlhua/article/details/12905109

A. 一个列上不能有两个约束,错误，例如非空+唯一可以作用在一个列上。

B. 如果数据已经存在不能添加主键约束，错误，有数据存在，也可以插入主键约束，前提是数据没有重复

C. 主键约束只有在表创建时才能被创建，错误。有了数据之后一样可以增加，不一定只有在创建的时候加

D. 即使数据存在，如果没有重复值，你能添加主键约束，正确。

 

实验验证，有数据是否可以加主键约束。

sh@TESTDB> create table orders

  2  (ord_id number(2) not null,

  3  cust_id number(3) not null,

  4  ord_date date not null,

  5  ord_amount number(10,2) not null)

  6  ;

Table created.

h@TESTDB> select * from orders;

    ORD_ID    CUST_ID ORD_DATE  ORD_AMOUNT

---------- ---------- --------- ----------

         1         11      03-SEP-13        111

         1         10      03-SEP-13        111

         2         22       03-SEP-13        222

         3         33       03-SEP-13        333

sh@TESTDB> alter table orders add constraint ord_pk primary key(ord_id);

alter table orders add constraint ord_pk primary key(ord_id)

                                  *

ERROR at line 1:

ORA-02437: cannot validate (SH.ORD_PK) - primary key violated

去掉ord_id重复值

sh@TESTDB> select * from orders;

    ORD_ID    CUST_ID ORD_DATE  ORD_AMOUNT

---------- ---------- --------- ----------

         1         11 03-SEP-13        111

         2         22 03-SEP-13        222

         3         33 03-SEP-13        333

sh@TESTDB> alter table orders add constraint ord_pk primary key(ord_id);

Table altered.